Parameter Query Again

[Guest]

I hope someone can help me.

I created a parameter query first, then I created a form where the record
source is based on the parameter query. Is there a way to display the
parameter query dialog box is the input doesn't match one of the records from
the table?

For example, when I open the Form "frmStudents", a parameter dialog pops up
'Enter Student ID'. If I enter a student id that matches a record from the
table, that student's record is displayed within the form. BUT if the student
id DOESN'T match any records in a table, I want the parameter dialog box to
appear again until the Student ID entered matches a record from the
table..like a LOOP.

Thank you in advance.

 

[Van T. Dinh]

In stead of the Parameter Query on your real Form. I would normally open
another Form "frmPreProcess" where the user enter / select the StudentID in
a Form Control (TextBox or ComboBox). This Form also has a CommandButton to
open the real Form "frmStudents"

Open the Form "frmPreProcess" so that the user can enter the required
StudentID and then click the CommandButton to open the Form "frmStudents".
In the Click Event procedure, check that the entered StudentID exists and
open the Form "frmStudents" using the Control on the Form "frmPreProcess" as
the Parameter. If the StudentID doesn't exist, take appropriate action.

You can, of course, use just a single Form based on a non-parametrised Query
and provide an unbound ComboBox for the user to select the required Student.

 

[Guest]

Your logic makes sense Van, but since I'm not really good at Access and
surely don't know Event Procedure, what exactly do I need to type in the
Click Event Procedure to make this work?

I apologize for my ignorance.

 

[Van T. Dinh]

The code should be something like:

If DCount("*", "tblSudents", "[StudentID] = " & Me.txtStudentID) > 0 Then
' StudentID exists
DoCmd.OpenForm "frmStudents", , , "[StudentID] = " & Me.txtStudentID
Else
' StudentID doesn't exist
MsgBox "StudentID incorrect. Please re-enter StudentID."
End If

 

[Guest]

Hi Van,

I tried to enter the argument in the Event Procedure and received an error
message "Compile error: Method or data member not found". Below is my Even
Procedure. Can you tell me what I'm doing wrong here? Thank you for help.

Private Sub openfrmThuha_Click()
If DCount("*", "tblThuha", "[AUID] = " & Me.txtAUID) > 0 Then
'AUID exists
DoCmd.OpenForm "frmThuha", , , "[AUID]=" & Me.txtAUID
Else
'StudentID doesn't exist
MsgBox "AU ID incorrect. Please re-enter AU ID."
End If

On Error GoTo Err_openfrmThuha_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmThuha"

stLinkCriteria = "[AU ID]=" & Me![Combo4]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_openfrmThuha_Click:
Exit Sub

Err_openfrmThuha_Click:
MsgBox Err.Description
Resume Exit_openfrmThuha_Click

End Sub

The code should be something like:

If DCount("*", "tblSudents", "[StudentID] = " & Me.txtStudentID) > 0 Then
' StudentID exists
DoCmd.OpenForm "frmStudents", , , "[StudentID] = " & Me.txtStudentID
Else
' StudentID doesn't exist
MsgBox "StudentID incorrect. Please re-enter StudentID."
End If

--
HTH
Van T. Dinh
MVP (Access)

Your logic makes sense Van, but since I'm not really good at Access and
surely don't know Event Procedure, what exactly do I need to type in the
Click Event Procedure to make this work?

I apologize for my ignorance.

 

[Van T. Dinh]

Sorry, I am not sure what you did???

Do you have a CommandButton named "openfrmThuha" on the current Form?

If you do, what did you enter in the "On Click" row in the Event tab of the
Properties window of the CommandButton?

If you don't know how to use Events and to create Event Procedure codes,
suggest you check Access Help on preferably, read up on an Access
programming book.

 

[Guest]

Hi Van,

I do have a CommandButton named "openfrmThuha" and in the "On Click" row of
the Event tab of the Properties window of the CommandButton, I have entered
the following:

Private Sub frmThuha_Click()
If DCount("*", "tblThuha", "[AUID]=" & Me.AUID) > 0 Then
'StudentID exists
DoCmd.OpenForm "frmThuha", , , "[AUID]=" & Me.AUID
Else
'StudentID doesn't exist
MsgBox "Student ID doesn't exist. Please re-enter Student ID."
End If

End Sub

AUID is not a text field, but an autonumber, so I slightly changed the code
a bit. The form is somewhat half done. Thanks for being patient with me and
hopefully you can guide me to correct my problems.

When I open the frmPreProcess, the AUID field automatically has a record in
it...i.e. 123. Is there way to make the form opens with nothing inside the
AUID field? As of right now, if I click the submit button, it'll bring up
student's record 123 which is correct. If I replaced 123 with 689 and clicked
the submit button, it'll display "Student ID doesn't exist. Please re-enter
Student ID" and there's an OK button. That's perfect. The problem NOW is when
I close the frmPreProcess and re-open it, the number 689 appears in the AUID
field and when I click the Submit button, the form appears with 689 in the
AUID field. It should give me the message box that the student ID doesn't
match, please enter the student ID again. What am I doing wrong? If there's
a way for me to send you the db, please let me know.

Thank you for being patience with me.

Sorry, I am not sure what you did???

Do you have a CommandButton named "openfrmThuha" on the current Form?

If you do, what did you enter in the "On Click" row in the Event tab of the
Properties window of the CommandButton?

If you don't know how to use Events and to create Event Procedure codes,
suggest you check Access Help on preferably, read up on an Access
programming book.

--
HTH
Van T. Dinh
MVP (Access)

Hi Van,

I tried to enter the argument in the Event Procedure and received an error
message "Compile error: Method or data member not found". Below is my Even
Procedure. Can you tell me what I'm doing wrong here? Thank you for help.

Private Sub openfrmThuha_Click()
If DCount("*", "tblThuha", "[AUID] = " & Me.txtAUID) > 0 Then
'AUID exists
DoCmd.OpenForm "frmThuha", , , "[AUID]=" & Me.txtAUID
Else
'StudentID doesn't exist
MsgBox "AU ID incorrect. Please re-enter AU ID."
End If

On Error GoTo Err_openfrmThuha_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmThuha"

stLinkCriteria = "[AU ID]=" & Me![Combo4]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_openfrmThuha_Click:
Exit Sub

Err_openfrmThuha_Click:
MsgBox Err.Description
Resume Exit_openfrmThuha_Click

End Sub

 

[Van T. Dinh]

You cannot enter the VBA code in the "On Click" row.

In the "On Click" row, select "[Event Procedure]" from the drop-down list.
You should see the "Build" button with 3 dots just to the right on the ros.
Click the "Build" button and Access will take you to the Class Modiule
associated with the Form and this is where you enter the VBA code.

Sorry, I got no idea what you did with 123 or 689 since I can't see your
Form. However, it sounds like you need a "Navigation" ComboBox so that the
user can select the right Student by name which is a lot easier than by
StudentID. The ComboBox Wizard can create this sort of ComboBox appropriate
code for you.

 

[Guest]

A friend of mine was able to help. He added a few more codes to the Class
Module. I'm sorry that I didn't use the right terminology, but I did click
the "Build" button and it took me VBA screen where I can enter the VBA code.
Everything looks great now. Thank you for being so patience with me.

I'll post the VBA code tomorrow in case someone needs it. The database is at
my office and I don't remember all the codes.

You cannot enter the VBA code in the "On Click" row.

In the "On Click" row, select "[Event Procedure]" from the drop-down list.
You should see the "Build" button with 3 dots just to the right on the ros.
Click the "Build" button and Access will take you to the Class Modiule
associated with the Form and this is where you enter the VBA code.

Sorry, I got no idea what you did with 123 or 689 since I can't see your
Form. However, it sounds like you need a "Navigation" ComboBox so that the
user can select the right Student by name which is a lot easier than by
StudentID. The ComboBox Wizard can create this sort of ComboBox appropriate
code for you.

--
HTH
Van T. Dinh
MVP (Access)

Hi Van,

I do have a CommandButton named "openfrmThuha" and in the "On Click" row
of
the Event tab of the Properties window of the CommandButton, I have
entered
the following:

Private Sub frmThuha_Click()
If DCount("*", "tblThuha", "[AUID]=" & Me.AUID) > 0 Then
'StudentID exists
DoCmd.OpenForm "frmThuha", , , "[AUID]=" & Me.AUID
Else
'StudentID doesn't exist
MsgBox "Student ID doesn't exist. Please re-enter Student ID."
End If

End Sub

AUID is not a text field, but an autonumber, so I slightly changed the
code
a bit. The form is somewhat half done. Thanks for being patient with me
and
hopefully you can guide me to correct my problems.

When I open the frmPreProcess, the AUID field automatically has a record
in
it...i.e. 123. Is there way to make the form opens with nothing inside the
AUID field? As of right now, if I click the submit button, it'll bring up
student's record 123 which is correct. If I replaced 123 with 689 and
clicked
the submit button, it'll display "Student ID doesn't exist. Please
re-enter
Student ID" and there's an OK button. That's perfect. The problem NOW is
when
I close the frmPreProcess and re-open it, the number 689 appears in the
AUID
field and when I click the Submit button, the form appears with 689 in the
AUID field. It should give me the message box that the student ID doesn't
match, please enter the student ID again. What am I doing wrong? If
there's
a way for me to send you the db, please let me know.

Thank you for being patience with me.