Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Multiple combo boxes to select desired entry
- Thread starter ksgirl2
- Start date
It's quite possible.
Create a sub routine for each combo box to update it's list based on the contents of the others. Set the AfterUpdate event for each combo box to [Event Procedure] and call the update routines for the other 2 combo boxes from each of the AfterUpdate events.
For example, the first name AfterUpdate event would call the update procedure for Last Name and Medical Record Number.
Private Sub cboFirstName_AfterUpdate()
cboLastName_ListUpdate
cboMedRec_ListUpdate
End Sub
The LastName List update would look like this:
Sub cboLastName_ListUpdate()
Dim strSQL
Dim strWHERE
Dim strAND
'initialize WHERE string
strWHERE = "WHERE "
'Add criteria for FirstName
If Not IsNull(cboFirstName) Then
strWHERE = strWHERE & "FirstName='" & cboFirstName & "'"
strAND = " AND "
End If
'Add criteria for MedRec
If Not IsNull(cboMedRec) Then
strWHERE = strWHERE & strAND & "MedRec='" & cboMedRec & "'"
End If
'if no other options, set WHERE string to an empty string
If strWHERE = "WHERE " Then
strWHERE = ""
End If
strSQL = "SELECT DISTINCT Loc" & vbCrLf
strSQL = strSQL & "FROM Patients " & vbCrLf
strSQL = strSQL & strWHERE
Debug.Print strWHERE
Debug.Print strSQL
cboLoc.RowSource = strSQL
End Sub
_________
Rott Paws
...It's not a bug. It's an undocumented feature!!!
Create a sub routine for each combo box to update it's list based on the contents of the others. Set the AfterUpdate event for each combo box to [Event Procedure] and call the update routines for the other 2 combo boxes from each of the AfterUpdate events.
For example, the first name AfterUpdate event would call the update procedure for Last Name and Medical Record Number.
Private Sub cboFirstName_AfterUpdate()
cboLastName_ListUpdate
cboMedRec_ListUpdate
End Sub
The LastName List update would look like this:
Sub cboLastName_ListUpdate()
Dim strSQL
Dim strWHERE
Dim strAND
'initialize WHERE string
strWHERE = "WHERE "
'Add criteria for FirstName
If Not IsNull(cboFirstName) Then
strWHERE = strWHERE & "FirstName='" & cboFirstName & "'"
strAND = " AND "
End If
'Add criteria for MedRec
If Not IsNull(cboMedRec) Then
strWHERE = strWHERE & strAND & "MedRec='" & cboMedRec & "'"
End If
'if no other options, set WHERE string to an empty string
If strWHERE = "WHERE " Then
strWHERE = ""
End If
strSQL = "SELECT DISTINCT Loc" & vbCrLf
strSQL = strSQL & "FROM Patients " & vbCrLf
strSQL = strSQL & strWHERE
Debug.Print strWHERE
Debug.Print strSQL
cboLoc.RowSource = strSQL
End Sub
_________
Rott Paws
...It's not a bug. It's an undocumented feature!!!
- Thread starter
- #3
Well I've created a Sub cboLastName and Sub cboFirstname and Sub cboMRNumber and that has worked I guess but I can't tell because when I go to activate it I get an error and the debugger takes me to the
cboLoc.RowSource = strSQL
line. Do I need to enter in something that I'm forgetting? Sorry I'm rather new to this. Thank you for your help.
marie
cboLoc.RowSource = strSQL
line. Do I need to enter in something that I'm forgetting? Sorry I'm rather new to this. Thank you for your help.
marie
What error are you getting before it takes you to the line in the code window?
Without knowing what the error is, I suspect there's something in the SQL statement that it doesn't like.
My first guess is that you have a number or text field that is not handled properly in the WHERE clause.
A text field needs to have quotes around the criteria in the where clause. i.e WHERE FirstName='Bill'
A number field should not have quotes around the criteria. i.e. WHERE MedRec=12345
In the example code above, I have it inserting the quotes around all criteria. This will work if your Medical record numbers are alphanumeric i.e. MedRec='A4321' If your medical record numbers are numeric, the quotes need to be removed.
The two debug.print lines in the code will print the WHERE clause and the complete SQL statement, respectively, to the debug window.
When you get the error, open the debug window and look at the actual SQL that is contained in the strWHERE variable. That is what it is trying to pass to the database engine to set the rowsource.
You can copy that and paste it into the SQL window of a new query and try to run it. If it doesn't work, then you need to make changes to the code to get a good SQL statement. _________
Rott Paws
...It's not a bug. It's an undocumented feature!!!
Without knowing what the error is, I suspect there's something in the SQL statement that it doesn't like.
My first guess is that you have a number or text field that is not handled properly in the WHERE clause.
A text field needs to have quotes around the criteria in the where clause. i.e WHERE FirstName='Bill'
A number field should not have quotes around the criteria. i.e. WHERE MedRec=12345
In the example code above, I have it inserting the quotes around all criteria. This will work if your Medical record numbers are alphanumeric i.e. MedRec='A4321' If your medical record numbers are numeric, the quotes need to be removed.
The two debug.print lines in the code will print the WHERE clause and the complete SQL statement, respectively, to the debug window.
When you get the error, open the debug window and look at the actual SQL that is contained in the strWHERE variable. That is what it is trying to pass to the database engine to set the rowsource.
You can copy that and paste it into the SQL window of a new query and try to run it. If it doesn't work, then you need to make changes to the code to get a good SQL statement. _________
Rott Paws
...It's not a bug. It's an undocumented feature!!!
- Thread starter
- #5
Sorry I'm so slow at this. Thank you for your help. When I try to run it i get a window that pops up and says " RUN-TIME error '424' Object Required." So i think click debug and it takes me to the highlighted cboLoc.RowSource = strSQL ... now here is my entire code. I know this is long, i'm sorry.
Sub cboLastName_ListUpdate()
Dim strSQL
Dim strWHERE
Dim strAND
'initialize WHERE string
strWHERE = "WHERE"
'Add Criteria for MRNumber
If Not IsNull(cboMRNumber) Then
strWHERE = strWHERE & "MRNumber='" & cboMRNumber & "'"
strAND = "AND"
End If
'Add Criteria for FirstName
If Not IsNull(cboFirstName) Then
strWHERE = strWHERE & strAND & "FirstName='" & cboFirstName & "'"
End If
strSQL = "SELECT DISTINCT Loc" & vbCrLf
strSQL = strSQL & "FROM tbl_patients" & vbCrLf
strSQL = strSQL & strWHERE
Debug.Print strWHERE
Debug.Print strSQL
cboLoc.RowSource = strSQL
End Sub
Sub cboFirstName_ListUpdate()
Dim strSQL
Dim strWHERE
Dim strAND
'initialize WHERE string
strWHERE = "WHERE"
'Add Criteria for MRNumber
If Not IsNull(cboMRNumber) Then
strWHERE = strWHERE & "MRNumber='" & cboMRNumber & "'"
strAND = "AND"
End If
'Add Criteria for LastName
If Not IsNull(cboLastName) Then
strWHERE = strWHERE & strAND & "LastName='" & cboLastName & "'"
End If
strSQL = "SELECT DISTINCT Loc" & vbCrLf
strSQL = strSQL & "FROM tbl_patients" & vbCrLf
strSQL = strSQL & strWHERE
Debug.Print strWHERE
Debug.Print strSQL
cboLoc.RowSource = strSQL
End Sub
End Sub
Sub cboMRNumber_ListUpdate()
Dim strSQL
Dim strWHERE
Dim strAND
'initialize WHERE string
strWHERE = "WHERE"
'Add Criteria for FirstName
If Not IsNull(cboFirstName) Then
strWHERE = strWHERE & "FirstName='" & cboFirstName & "'"
strAND = "AND"
End If
'Add Criteria for LastName
If Not IsNull(cboLastName) Then
strWHERE = strWHERE & strAND & "LastName='" & cboLastName & "'"
End If
strSQL = "SELECT DISTINCT Loc" & vbCrLf
strSQL = strSQL & "FROM tbl_patients" & vbCrLf
strSQL = strSQL & strWHERE
Debug.Print strWHERE
Debug.Print strSQL
cboLoc.RowSource = strSQL
End Sub
Private Sub FirstName_AfterUpdate()
cboLastName_ListUpdate
cboMRNumber_ListUpdate
End Sub
Private Sub LastName_AfterUpdate()
cboMRNumber_ListUpdate
cboFirstName_ListUpdate
End Sub
Private Sub MRNumber_AfterUpdate()
cboLastName_ListUpdate
cboFirstName_ListUpdate
End Sub
Thank you so much for your help.
marie
Sub cboLastName_ListUpdate()
Dim strSQL
Dim strWHERE
Dim strAND
'initialize WHERE string
strWHERE = "WHERE"
'Add Criteria for MRNumber
If Not IsNull(cboMRNumber) Then
strWHERE = strWHERE & "MRNumber='" & cboMRNumber & "'"
strAND = "AND"
End If
'Add Criteria for FirstName
If Not IsNull(cboFirstName) Then
strWHERE = strWHERE & strAND & "FirstName='" & cboFirstName & "'"
End If
strSQL = "SELECT DISTINCT Loc" & vbCrLf
strSQL = strSQL & "FROM tbl_patients" & vbCrLf
strSQL = strSQL & strWHERE
Debug.Print strWHERE
Debug.Print strSQL
cboLoc.RowSource = strSQL
End Sub
Sub cboFirstName_ListUpdate()
Dim strSQL
Dim strWHERE
Dim strAND
'initialize WHERE string
strWHERE = "WHERE"
'Add Criteria for MRNumber
If Not IsNull(cboMRNumber) Then
strWHERE = strWHERE & "MRNumber='" & cboMRNumber & "'"
strAND = "AND"
End If
'Add Criteria for LastName
If Not IsNull(cboLastName) Then
strWHERE = strWHERE & strAND & "LastName='" & cboLastName & "'"
End If
strSQL = "SELECT DISTINCT Loc" & vbCrLf
strSQL = strSQL & "FROM tbl_patients" & vbCrLf
strSQL = strSQL & strWHERE
Debug.Print strWHERE
Debug.Print strSQL
cboLoc.RowSource = strSQL
End Sub
End Sub
Sub cboMRNumber_ListUpdate()
Dim strSQL
Dim strWHERE
Dim strAND
'initialize WHERE string
strWHERE = "WHERE"
'Add Criteria for FirstName
If Not IsNull(cboFirstName) Then
strWHERE = strWHERE & "FirstName='" & cboFirstName & "'"
strAND = "AND"
End If
'Add Criteria for LastName
If Not IsNull(cboLastName) Then
strWHERE = strWHERE & strAND & "LastName='" & cboLastName & "'"
End If
strSQL = "SELECT DISTINCT Loc" & vbCrLf
strSQL = strSQL & "FROM tbl_patients" & vbCrLf
strSQL = strSQL & strWHERE
Debug.Print strWHERE
Debug.Print strSQL
cboLoc.RowSource = strSQL
End Sub
Private Sub FirstName_AfterUpdate()
cboLastName_ListUpdate
cboMRNumber_ListUpdate
End Sub
Private Sub LastName_AfterUpdate()
cboMRNumber_ListUpdate
cboFirstName_ListUpdate
End Sub
Private Sub MRNumber_AfterUpdate()
cboLastName_ListUpdate
cboFirstName_ListUpdate
End Sub
Thank you so much for your help.
marie
The error is basically telling you that it can't find the table and field names in your query. You have to replace the field and table names in the queries that are being built by the code.
Replace the "Loc" in the queries with the actual field names that you want in the drop-down.
Replace the "tbl_patients" with the actual table name in your database.
For example, if the table is named Patient_Info and you have fields name F_Name, L_Name, and MRec, your code would look like the following for First Name:
strSQL = "SELECT DISTINCT F_Name" & vbCrLf
strSQL = strSQL & "FROM Patient_Info" & vbCrLf
Also in the If...Then statements that build the WHERE clause, you need to substitue your actual field names. As in the above example, if your field for the patient's last name is called L_Name, you would enter that in place of where I had "LastName"
_________
Rott Paws
...It's not a bug. It's an undocumented feature!!!
Replace the "Loc" in the queries with the actual field names that you want in the drop-down.
Replace the "tbl_patients" with the actual table name in your database.
For example, if the table is named Patient_Info and you have fields name F_Name, L_Name, and MRec, your code would look like the following for First Name:
strSQL = "SELECT DISTINCT F_Name" & vbCrLf
strSQL = strSQL & "FROM Patient_Info" & vbCrLf
Also in the If...Then statements that build the WHERE clause, you need to substitue your actual field names. As in the above example, if your field for the patient's last name is called L_Name, you would enter that in place of where I had "LastName"
_________
Rott Paws
...It's not a bug. It's an undocumented feature!!!
- Thread starter
- #7
Thanks I forgot that LOC! I still don't think I'm getting this quite right. My table is really named tbl_patients and the three entities are
FirstName
LastName and
MRNumber exactly like that. I made the appropriate changes. Do you see any left out code or mis-named entries?? Thank you again for all your help.
my code is as follows:
Sub cboLastName_ListUpdate()
Dim strSQL
Dim strWHERE
Dim strAND
'initialize WHERE string
strWHERE = "WHERE"
'Add Criteria for MRNumber
If Not IsNull(cboMRNumber) Then
strWHERE = strWHERE & "MRNumber='" & cboMRNumber & "'"
strAND = "AND"
End If
'Add Criteria for FirstName
If Not IsNull(cboFirstName) Then
strWHERE = strWHERE & strAND & "FirstName='" & cboFirstName & "'"
End If
'if no other options, set WHERE string to an empty string
If strWHERE = "WHERE" Then
strWHERE = ""
End If
strSQL = "SELECT DISTINCT FirstName" & vbCrLf
strSQL = strSQL & "FROM tbl_patients" & vbCrLf
strSQL = strSQL & strWHERE
Debug.Print strWHERE
Debug.Print strSQL
cboLastName.tbl_patients = strSQL
End Sub
Sub cboFirstName_ListUpdate()
Dim strSQL
Dim strWHERE
Dim strAND
'initialize WHERE string
strWHERE = "WHERE"
'Add Criteria for MRNumber
If Not IsNull(cboMRNumber) Then
strWHERE = strWHERE & "MR Number='" & cboMRNumber & "'"
strAND = "AND"
End If
'Add Criteria for LastName
If Not IsNull(cboLastName) Then
strWHERE = strWHERE & strAND & "LastName='" & cboLastName & "'"
End If
'if no other options, set WHERE string to an empty string
If strWHERE = "WHERE" Then
strWHERE = ""
End If
strSQL = "SELECT DISTINCT LastName" & vbCrLf
strSQL = strSQL & "FROM tbl_patients" & vbCrLf
strSQL = strSQL & strWHERE
Debug.Print strWHERE
Debug.Print strSQL
cboFirstName.tbl_patients = strSQL
End Sub
End Sub
Sub cboMRNumber_ListUpdate()
Dim strSQL
Dim strWHERE
Dim strAND
'initialize WHERE string
strWHERE = "WHERE"
'Add Criteria for FirstName
If Not IsNull(cboFirstName) Then
strWHERE = strWHERE & "FirstName='" & cboFirstName & "'"
strAND = "AND"
End If
'Add Criteria for LastName
If Not IsNull(cboLastName) Then
strWHERE = strWHERE & strAND & "LastName='" & cboLastName & "'"
End If
'if no other options, set WHERE string to an empty string
If strWHERE = "WHERE" Then
strWHERE = ""
End If
strSQL = "SELECT DISTINCT LastName" & vbCrLf
strSQL = strSQL & "FROM tbl_patients" & vbCrLf
strSQL = strSQL & strWHERE
Debug.Print strWHERE
Debug.Print strSQL
cboMRNumber.tbl_patients = strSQL
End Sub
Private Sub FirstName_AfterUpdate()
cboLastName_ListUpdate
cboMRNumber_ListUpdate
End Sub
Private Sub LastName_AfterUpdate()
cboMRNumber_ListUpdate
cboFirstName_ListUpdate
End Sub
Private Sub MRNumber_AfterUpdate()
cboLastName_ListUpdate
cboFirstName_ListUpdate
End Sub
thanks
marie
FirstName
LastName and
MRNumber exactly like that. I made the appropriate changes. Do you see any left out code or mis-named entries?? Thank you again for all your help.
my code is as follows:
Sub cboLastName_ListUpdate()
Dim strSQL
Dim strWHERE
Dim strAND
'initialize WHERE string
strWHERE = "WHERE"
'Add Criteria for MRNumber
If Not IsNull(cboMRNumber) Then
strWHERE = strWHERE & "MRNumber='" & cboMRNumber & "'"
strAND = "AND"
End If
'Add Criteria for FirstName
If Not IsNull(cboFirstName) Then
strWHERE = strWHERE & strAND & "FirstName='" & cboFirstName & "'"
End If
'if no other options, set WHERE string to an empty string
If strWHERE = "WHERE" Then
strWHERE = ""
End If
strSQL = "SELECT DISTINCT FirstName" & vbCrLf
strSQL = strSQL & "FROM tbl_patients" & vbCrLf
strSQL = strSQL & strWHERE
Debug.Print strWHERE
Debug.Print strSQL
cboLastName.tbl_patients = strSQL
End Sub
Sub cboFirstName_ListUpdate()
Dim strSQL
Dim strWHERE
Dim strAND
'initialize WHERE string
strWHERE = "WHERE"
'Add Criteria for MRNumber
If Not IsNull(cboMRNumber) Then
strWHERE = strWHERE & "MR Number='" & cboMRNumber & "'"
strAND = "AND"
End If
'Add Criteria for LastName
If Not IsNull(cboLastName) Then
strWHERE = strWHERE & strAND & "LastName='" & cboLastName & "'"
End If
'if no other options, set WHERE string to an empty string
If strWHERE = "WHERE" Then
strWHERE = ""
End If
strSQL = "SELECT DISTINCT LastName" & vbCrLf
strSQL = strSQL & "FROM tbl_patients" & vbCrLf
strSQL = strSQL & strWHERE
Debug.Print strWHERE
Debug.Print strSQL
cboFirstName.tbl_patients = strSQL
End Sub
End Sub
Sub cboMRNumber_ListUpdate()
Dim strSQL
Dim strWHERE
Dim strAND
'initialize WHERE string
strWHERE = "WHERE"
'Add Criteria for FirstName
If Not IsNull(cboFirstName) Then
strWHERE = strWHERE & "FirstName='" & cboFirstName & "'"
strAND = "AND"
End If
'Add Criteria for LastName
If Not IsNull(cboLastName) Then
strWHERE = strWHERE & strAND & "LastName='" & cboLastName & "'"
End If
'if no other options, set WHERE string to an empty string
If strWHERE = "WHERE" Then
strWHERE = ""
End If
strSQL = "SELECT DISTINCT LastName" & vbCrLf
strSQL = strSQL & "FROM tbl_patients" & vbCrLf
strSQL = strSQL & strWHERE
Debug.Print strWHERE
Debug.Print strSQL
cboMRNumber.tbl_patients = strSQL
End Sub
Private Sub FirstName_AfterUpdate()
cboLastName_ListUpdate
cboMRNumber_ListUpdate
End Sub
Private Sub LastName_AfterUpdate()
cboMRNumber_ListUpdate
cboFirstName_ListUpdate
End Sub
Private Sub MRNumber_AfterUpdate()
cboLastName_ListUpdate
cboFirstName_ListUpdate
End Sub
thanks
marie
In sub cboFirstName_ListUpdate, you have a space in "MR Number" in the WHERE clause.
Also, like I said before, if MRNumber is a number data type, then remove the single-quotes that are being placed around it in the WHERE clause. If it's a text data type, it is fine the way it is. But if it's a number it should look like:
strWHERE = strWHERE & "MRNumber=" & cboMRNumber
instead of
strWHERE = strWHERE & "MRNumber='" & cboMRNumber & "'"
If you still have problems after making these changes, copy the SQL from the debug window and paste that here so I can take a look at the actual SQL that's going to the db engine. _________
Rott Paws
...It's not a bug. It's an undocumented feature!!!
Also, like I said before, if MRNumber is a number data type, then remove the single-quotes that are being placed around it in the WHERE clause. If it's a text data type, it is fine the way it is. But if it's a number it should look like:
strWHERE = strWHERE & "MRNumber=" & cboMRNumber
instead of
strWHERE = strWHERE & "MRNumber='" & cboMRNumber & "'"
If you still have problems after making these changes, copy the SQL from the debug window and paste that here so I can take a look at the actual SQL that's going to the db engine. _________
Rott Paws
...It's not a bug. It's an undocumented feature!!!
- Thread starter
- #9
Thank you for all your help. I fixed the MRNumber issue. It is a number. I still get the same error with the cboLastName.tbl_patients = strSQL line. It's the same error if I click on the MRNumber drop down, the First Name or the Last Name. Thank you for all your help. My new code is as follows.
Thanks again,
marie
Sub cboLastName_ListUpdate()
Dim strSQL
Dim strWHERE
Dim strAND
'initialize WHERE string
strWHERE = "WHERE"
'Add Criteria for MRNumber
If Not IsNull(cboMRNumber) Then
strWHERE = strWHERE & "MRNumber=" & cboMRNumber
strAND = "AND"
End If
'Add Criteria for FirstName
If Not IsNull(cboFirstName) Then
strWHERE = strWHERE & strAND & "FirstName='" & cboFirstName & "'"
End If
'if no other options, set WHERE string to an empty string
If strWHERE = "WHERE" Then
strWHERE = ""
End If
strSQL = "SELECT DISTINCT FirstName" & vbCrLf
strSQL = strSQL & "FROM tbl_patients" & vbCrLf
strSQL = strSQL & strWHERE
Debug.Print strWHERE
Debug.Print strSQL
cboLastName.tbl_patients = strSQL
End Sub
Sub cboFirstName_ListUpdate()
Dim strSQL
Dim strWHERE
Dim strAND
'initialize WHERE string
strWHERE = "WHERE"
'Add Criteria for MRNumber
If Not IsNull(cboMRNumber) Then
strWHERE = strWHERE & "MRNumber=" & cboMRNumber
strAND = "AND"
End If
'Add Criteria for LastName
If Not IsNull(cboLastName) Then
strWHERE = strWHERE & strAND & "LastName='" & cboLastName & "'"
End If
'if no other options, set WHERE string to an empty string
If strWHERE = "WHERE" Then
strWHERE = ""
End If
strSQL = "SELECT DISTINCT LastName" & vbCrLf
strSQL = strSQL & "FROM tbl_patients" & vbCrLf
strSQL = strSQL & strWHERE
Debug.Print strWHERE
Debug.Print strSQL
cboFirstName.tbl_patients = strSQL
End Sub
End Sub
Sub cboMRNumber_ListUpdate()
Dim strSQL
Dim strWHERE
Dim strAND
'initialize WHERE string
strWHERE = "WHERE"
'Add Criteria for FirstName
If Not IsNull(cboFirstName) Then
strWHERE = strWHERE & "FirstName='" & cboFirstName & "'"
strAND = "AND"
End If
'Add Criteria for LastName
If Not IsNull(cboLastName) Then
strWHERE = strWHERE & strAND & "LastName='" & cboLastName & "'"
End If
'if no other options, set WHERE string to an empty string
If strWHERE = "WHERE" Then
strWHERE = ""
End If
strSQL = "SELECT DISTINCT LastName" & vbCrLf
strSQL = strSQL & "FROM tbl_patients" & vbCrLf
strSQL = strSQL & strWHERE
Debug.Print strWHERE
Debug.Print strSQL
cboMRNumber.tbl_patients = strSQL
End Sub
Private Sub FirstName_AfterUpdate()
cboLastName_ListUpdate
cboMRNumber_ListUpdate
End Sub
Private Sub LastName_AfterUpdate()
cboMRNumber_ListUpdate
cboFirstName_ListUpdate
End Sub
Private Sub MRNumber_AfterUpdate()
cboLastName_ListUpdate
cboFirstName_ListUpdate
End Sub
Thanks again,
marie
Sub cboLastName_ListUpdate()
Dim strSQL
Dim strWHERE
Dim strAND
'initialize WHERE string
strWHERE = "WHERE"
'Add Criteria for MRNumber
If Not IsNull(cboMRNumber) Then
strWHERE = strWHERE & "MRNumber=" & cboMRNumber
strAND = "AND"
End If
'Add Criteria for FirstName
If Not IsNull(cboFirstName) Then
strWHERE = strWHERE & strAND & "FirstName='" & cboFirstName & "'"
End If
'if no other options, set WHERE string to an empty string
If strWHERE = "WHERE" Then
strWHERE = ""
End If
strSQL = "SELECT DISTINCT FirstName" & vbCrLf
strSQL = strSQL & "FROM tbl_patients" & vbCrLf
strSQL = strSQL & strWHERE
Debug.Print strWHERE
Debug.Print strSQL
cboLastName.tbl_patients = strSQL
End Sub
Sub cboFirstName_ListUpdate()
Dim strSQL
Dim strWHERE
Dim strAND
'initialize WHERE string
strWHERE = "WHERE"
'Add Criteria for MRNumber
If Not IsNull(cboMRNumber) Then
strWHERE = strWHERE & "MRNumber=" & cboMRNumber
strAND = "AND"
End If
'Add Criteria for LastName
If Not IsNull(cboLastName) Then
strWHERE = strWHERE & strAND & "LastName='" & cboLastName & "'"
End If
'if no other options, set WHERE string to an empty string
If strWHERE = "WHERE" Then
strWHERE = ""
End If
strSQL = "SELECT DISTINCT LastName" & vbCrLf
strSQL = strSQL & "FROM tbl_patients" & vbCrLf
strSQL = strSQL & strWHERE
Debug.Print strWHERE
Debug.Print strSQL
cboFirstName.tbl_patients = strSQL
End Sub
End Sub
Sub cboMRNumber_ListUpdate()
Dim strSQL
Dim strWHERE
Dim strAND
'initialize WHERE string
strWHERE = "WHERE"
'Add Criteria for FirstName
If Not IsNull(cboFirstName) Then
strWHERE = strWHERE & "FirstName='" & cboFirstName & "'"
strAND = "AND"
End If
'Add Criteria for LastName
If Not IsNull(cboLastName) Then
strWHERE = strWHERE & strAND & "LastName='" & cboLastName & "'"
End If
'if no other options, set WHERE string to an empty string
If strWHERE = "WHERE" Then
strWHERE = ""
End If
strSQL = "SELECT DISTINCT LastName" & vbCrLf
strSQL = strSQL & "FROM tbl_patients" & vbCrLf
strSQL = strSQL & strWHERE
Debug.Print strWHERE
Debug.Print strSQL
cboMRNumber.tbl_patients = strSQL
End Sub
Private Sub FirstName_AfterUpdate()
cboLastName_ListUpdate
cboMRNumber_ListUpdate
End Sub
Private Sub LastName_AfterUpdate()
cboMRNumber_ListUpdate
cboFirstName_ListUpdate
End Sub
Private Sub MRNumber_AfterUpdate()
cboLastName_ListUpdate
cboFirstName_ListUpdate
End Sub
Replace the tbl_patients with Rowsource the the line that's failing for all three procedures.
For example,
cboLastName.tbl_patients = strSQL
shoud be
cboLastName.RowSource = strSQL _________
Rott Paws
...It's not a bug. It's an undocumented feature!!!
For example,
cboLastName.tbl_patients = strSQL
shoud be
cboLastName.RowSource = strSQL _________
Rott Paws
...It's not a bug. It's an undocumented feature!!!
- Thread starter
- #11
Don't give up. We're pretty close. It's failing on the last line of code before giving you what you want.
What version of Access are you using? I primarily use 97 for my development and then convert it up as needed. If you're using 2000 or XP, the syntax might be a little different and I'll need to play around with it to find out what works.
Also, if you could send me that SQL from the debug window, that might help. That would let me determine if it's a problem in the SQL rather than in the code.
_________
Rott Paws
...It's not a bug. It's an undocumented feature!!!
What version of Access are you using? I primarily use 97 for my development and then convert it up as needed. If you're using 2000 or XP, the syntax might be a little different and I'll need to play around with it to find out what works.
Also, if you could send me that SQL from the debug window, that might help. That would let me determine if it's a problem in the SQL rather than in the code.
_________
Rott Paws
...It's not a bug. It's an undocumented feature!!!
- Thread starter
- #14
When you hit Debug on the error message it should take you into the Code window.
In Access2k there are a few windows for debugging. The one I'm interested in now is the Immediate Window. While in the code window, press Ctrl+G or go to View/Immediate Window.
This will open a window at the bottom of the VB window. This is where the SQL will be printed on the debug.print statement. _________
Rott Paws
...It's not a bug. It's an undocumented feature!!!
In Access2k there are a few windows for debugging. The one I'm interested in now is the Immediate Window. While in the code window, press Ctrl+G or go to View/Immediate Window.
This will open a window at the bottom of the VB window. This is where the SQL will be printed on the debug.print statement. _________
Rott Paws
...It's not a bug. It's an undocumented feature!!!
- Thread starter
- #16
AHHH I have the immediate window and it looks like this
WHEREFirstName=''ANDLastName=''
SELECT DISTINCT LastName
FROM tbl_patients
WHEREFirstName=''ANDLastName=''
for when i try to use last name....and this...
WHEREMRNumber=ANDFirstName=''
SELECT DISTINCT FirstName
FROM tbl_patients
WHEREMRNumber=ANDFirstName=''
WHEREFirstName=''ANDLastName=''
SELECT DISTINCT LastName
FROM tbl_patients
WHEREFirstName=''ANDLastName=''
for when i try to use MRNumber and this....
WHEREMRNumber=ANDFirstName=''
SELECT DISTINCT FirstName
FROM tbl_patients
WHEREMRNumber=ANDFirstName=''
WHEREMRNumber=ANDFirstName=''
SELECT DISTINCT FirstName
FROM tbl_patients
WHEREMRNumber=ANDFirstName=''
WHEREFirstName=''ANDLastName=''
SELECT DISTINCT LastName
FROM tbl_patients
WHEREFirstName=''ANDLastName=''
for when i try and use the First Name. I could put my form and table on a seperate database and email them to ya.? just an idea.
thanks again
marie
WHEREFirstName=''ANDLastName=''
SELECT DISTINCT LastName
FROM tbl_patients
WHEREFirstName=''ANDLastName=''
for when i try to use last name....and this...
WHEREMRNumber=ANDFirstName=''
SELECT DISTINCT FirstName
FROM tbl_patients
WHEREMRNumber=ANDFirstName=''
WHEREFirstName=''ANDLastName=''
SELECT DISTINCT LastName
FROM tbl_patients
WHEREFirstName=''ANDLastName=''
for when i try to use MRNumber and this....
WHEREMRNumber=ANDFirstName=''
SELECT DISTINCT FirstName
FROM tbl_patients
WHEREMRNumber=ANDFirstName=''
WHEREMRNumber=ANDFirstName=''
SELECT DISTINCT FirstName
FROM tbl_patients
WHEREMRNumber=ANDFirstName=''
WHEREFirstName=''ANDLastName=''
SELECT DISTINCT LastName
FROM tbl_patients
WHEREFirstName=''ANDLastName=''
for when i try and use the First Name. I could put my form and table on a seperate database and email them to ya.? just an idea.
thanks again
marie
That helps a lot. The first thing that jumps out is you are missing spaces that should be in your SQL queries.
Basically what we are doing here is creating a query in a string and then assigning that string to the rowsource property of the combo box.
Dim strSQL as string creates a variable called strSQL.
strWHERE="WHERE" sets the variable strWHERE equal to the WHERE.
strWHERE=strWHERE & " MRNumber=" & cboMRNumber sets the value of strWHERE equal to whatever is already there (WHERE) plus what is in quotes added to the end of it (concatenated) plus whatever is currently in the combo box named cboMRNumber. For example, if the Medical Record combo box has record # 12345, this statement would make strWHERE contain WHERE MRNumber=12345
Your code is missing the space before MRNumber in the quotes. Spaces are also needed in the AND string. It should read strAND=strAND & " AND "
Adding in the spaces should change:
SELECT DISTINCT LastName
FROM tbl_patients
WHEREFirstName=''ANDLastName=''
to
SELECT DISTINCT LastName
FROM tbl_patients
WHERE FirstName='' AND LastName=''
which would be a valid SQL statement. This should at least stop you from getting the error, but it won't get you anything in the drop-down list.
(cont.) _________
Rott Paws
...It's not a bug. It's an undocumented feature!!!
Basically what we are doing here is creating a query in a string and then assigning that string to the rowsource property of the combo box.
Dim strSQL as string creates a variable called strSQL.
strWHERE="WHERE" sets the variable strWHERE equal to the WHERE.
strWHERE=strWHERE & " MRNumber=" & cboMRNumber sets the value of strWHERE equal to whatever is already there (WHERE) plus what is in quotes added to the end of it (concatenated) plus whatever is currently in the combo box named cboMRNumber. For example, if the Medical Record combo box has record # 12345, this statement would make strWHERE contain WHERE MRNumber=12345
Your code is missing the space before MRNumber in the quotes. Spaces are also needed in the AND string. It should read strAND=strAND & " AND "
Adding in the spaces should change:
SELECT DISTINCT LastName
FROM tbl_patients
WHEREFirstName=''ANDLastName=''
to
SELECT DISTINCT LastName
FROM tbl_patients
WHERE FirstName='' AND LastName=''
which would be a valid SQL statement. This should at least stop you from getting the error, but it won't get you anything in the drop-down list.
(cont.) _________
Rott Paws
...It's not a bug. It's an undocumented feature!!!
The next thing is the the field names in the ListUpdate procedures.
What you want to do is fill the combo box named cboLastName with all the Last Names in the database that have a first name matching what is in the First Name combo box (cboFirstName) and a/or a Medical record in the Medical Record combo box. If neither a First Name or Medical record is selected, then all last Names in the database should appear on the list.
In the strSQL = "SELECT DISTINCT xxx" lines of the database, xxx should be LastName for the cboLastName_ListUpdate() procedure, FirstName for the cboFirstName_ListUpdate() procedure, and MRNumber for cboMRNumber_ListUpdate().
-----------------------------------------------------------
The final problem is the blank criteria being inserted into your WHERE clause.
If Not IsNull(cboFirstName) Then tells the computer to check the value in the combo box named cboFirstName and if there is something there (it is not Null) then execute the following line(s). If the box does not have anything selected (it IS Null) then skip down to the End If line and then continue.
Your application seems to be executing the line in the If...Then block, but it is not adding anything to the SQL statement for criteria.
My only guess for this is that it has an empty string value which is different from Null, even though it means essentially the same thing to us humans.
Try changing the if lines from
If Not IsNull(cboMRNumber) Then
to
If Not IsNull(cboMRNumber) and cboMRNumber <>"" Then
If that still doesn't work, you can email me a copy of the form and table at rottpaws2000@yahoo.com and I'll take a look at it. _________
Rott Paws
...It's not a bug. It's an undocumented feature!!!
What you want to do is fill the combo box named cboLastName with all the Last Names in the database that have a first name matching what is in the First Name combo box (cboFirstName) and a/or a Medical record in the Medical Record combo box. If neither a First Name or Medical record is selected, then all last Names in the database should appear on the list.
In the strSQL = "SELECT DISTINCT xxx" lines of the database, xxx should be LastName for the cboLastName_ListUpdate() procedure, FirstName for the cboFirstName_ListUpdate() procedure, and MRNumber for cboMRNumber_ListUpdate().
-----------------------------------------------------------
The final problem is the blank criteria being inserted into your WHERE clause.
If Not IsNull(cboFirstName) Then tells the computer to check the value in the combo box named cboFirstName and if there is something there (it is not Null) then execute the following line(s). If the box does not have anything selected (it IS Null) then skip down to the End If line and then continue.
Your application seems to be executing the line in the If...Then block, but it is not adding anything to the SQL statement for criteria.
My only guess for this is that it has an empty string value which is different from Null, even though it means essentially the same thing to us humans.
Try changing the if lines from
If Not IsNull(cboMRNumber) Then
to
If Not IsNull(cboMRNumber) and cboMRNumber <>"" Then
If that still doesn't work, you can email me a copy of the form and table at rottpaws2000@yahoo.com and I'll take a look at it. _________
Rott Paws
...It's not a bug. It's an undocumented feature!!!
Marie,
I got your database. The main thing I found that I missed in your code above is your combo box controls were actually named MRNumber, LastName, and FirstName whereas the code called them cboMRNumber, cboLastName, and cboFirstName respctively.
The reason you were getting the Object required error is that the computer didn't have anything called cboMRNumber/cboLastName/cboFirstName, so it didn't know what your code was trying to manipulate. That's also why you were getting the empty criteria in the WHERE clause. The code was testing for whether cboMRNumber was Null, but it didn't exist, so it was Empty instead. _________
Rott Paws
...It's not a bug. It's an undocumented feature!!!
I got your database. The main thing I found that I missed in your code above is your combo box controls were actually named MRNumber, LastName, and FirstName whereas the code called them cboMRNumber, cboLastName, and cboFirstName respctively.
The reason you were getting the Object required error is that the computer didn't have anything called cboMRNumber/cboLastName/cboFirstName, so it didn't know what your code was trying to manipulate. That's also why you were getting the empty criteria in the WHERE clause. The code was testing for whether cboMRNumber was Null, but it didn't exist, so it was Empty instead. _________
Rott Paws
...It's not a bug. It's an undocumented feature!!!
I also added a Reset button to refresh the drop-down lists and clear the selection(s). I emailed it back to you.
Now that this part is working, you'll need to add a search/retrieve command button to go find the record you want to retrieve from your selection.
Check out this thread for building a search tool in Access: thread181-262790 _________
Rott Paws
...It's not a bug. It's an undocumented feature!!!
Now that this part is working, you'll need to add a search/retrieve command button to go find the record you want to retrieve from your selection.
Check out this thread for building a search tool in Access: thread181-262790 _________
Rott Paws
...It's not a bug. It's an undocumented feature!!!
- Status
Similar threads
