Hello everyone;
I'm very green to Microsoft Access.. (self teaching.. or trying to!).. I'm currently using Access 2003 ..
Here are my 2 problems;
1. I have 3 tables..
phone_data - names, positions, phone ext and alt phone ext
br_data - locations of bank machines and where to find them
machine_data - slot machine locations, and where to find them
How do I tell the combo box to look in all three of this tables to use as information to select from in the drop down menu?
2. Once you have selected a item from the drop down menu either with a click of the mouse or clicking on the command button the information from the other fields in the table will be populated to the text boxes. IE.. Click on the Dept Mgr and his name, position, phone extension will appear in the text boxes.
Here is the old code I was using (when it was set up where you would type in what you were looking for and click on the command button):
Private Sub Command30_Click()
Dim temp1, temp2, temp3, temp4, temp5, temp6, temp7, temp8, temp9, temp10
'Read the value from the textbox into a temporary variable
temp1 = Text6.Value
'Lookup the PTZ from the table
temp5 = DLookup("[ptz_num]", "machine_data", "[machine_num] = '" & temp1 & "'")
'Lookup the Asset from the table
temp6 = DLookup("[asset_num]", "machine_data", "[machine_num] = '" & temp1 & "'")
'Lookup the fixed camera from the table
temp2 = DLookup("[fixed_num]", "br_data", "[br_num] = '" & temp1 & "'")
'Lookup the PTZ from the table
temp3 = DLookup("[ptz_num]", "br_data", "[br_num] = '" & temp1 & "'")
'Lookup the fixed camera from the table
temp4 = DLookup("[fixed_num]", "machine_data", "[machine_num] = '" & temp1 & "'")
'Lookup the phone number from the table
temp7 = DLookup("[phone_num]", "phone_data", "[depart_num] = '" & temp1 & "'")
'Lookup the alt phone number from the table
temp8 = DLookup("[alt_phone_num]", "phone_data", "[depart_num] = '" & temp1 & "'")
'Lookup the person from the table
temp9 = DLookup("[person_num]", "phone_data", "[depart_num] = '" & temp1 & "'")
'Lookup the job title from the table
temp10 = DLookup("[depart_num]", "phone_data", "[depart_num] = '" & temp1 & "'")
'Output the data to the textboxes
Text54.Value = temp2
Text56.Value = temp3
Text91.Value = temp4
Text95.Value = temp5
Text93.Value = temp6
Text103.Value = temp7
Text105.Value = temp8
Text99.Value = temp9
Text101.Value = temp10
End Sub
Private Sub Text6_Click()
Text6 = vbNullString
End Sub
I'm very green to Microsoft Access.. (self teaching.. or trying to!).. I'm currently using Access 2003 ..
Here are my 2 problems;
1. I have 3 tables..
phone_data - names, positions, phone ext and alt phone ext
br_data - locations of bank machines and where to find them
machine_data - slot machine locations, and where to find them
How do I tell the combo box to look in all three of this tables to use as information to select from in the drop down menu?
2. Once you have selected a item from the drop down menu either with a click of the mouse or clicking on the command button the information from the other fields in the table will be populated to the text boxes. IE.. Click on the Dept Mgr and his name, position, phone extension will appear in the text boxes.
Here is the old code I was using (when it was set up where you would type in what you were looking for and click on the command button):
Private Sub Command30_Click()
Dim temp1, temp2, temp3, temp4, temp5, temp6, temp7, temp8, temp9, temp10
'Read the value from the textbox into a temporary variable
temp1 = Text6.Value
'Lookup the PTZ from the table
temp5 = DLookup("[ptz_num]", "machine_data", "[machine_num] = '" & temp1 & "'")
'Lookup the Asset from the table
temp6 = DLookup("[asset_num]", "machine_data", "[machine_num] = '" & temp1 & "'")
'Lookup the fixed camera from the table
temp2 = DLookup("[fixed_num]", "br_data", "[br_num] = '" & temp1 & "'")
'Lookup the PTZ from the table
temp3 = DLookup("[ptz_num]", "br_data", "[br_num] = '" & temp1 & "'")
'Lookup the fixed camera from the table
temp4 = DLookup("[fixed_num]", "machine_data", "[machine_num] = '" & temp1 & "'")
'Lookup the phone number from the table
temp7 = DLookup("[phone_num]", "phone_data", "[depart_num] = '" & temp1 & "'")
'Lookup the alt phone number from the table
temp8 = DLookup("[alt_phone_num]", "phone_data", "[depart_num] = '" & temp1 & "'")
'Lookup the person from the table
temp9 = DLookup("[person_num]", "phone_data", "[depart_num] = '" & temp1 & "'")
'Lookup the job title from the table
temp10 = DLookup("[depart_num]", "phone_data", "[depart_num] = '" & temp1 & "'")
'Output the data to the textboxes
Text54.Value = temp2
Text56.Value = temp3
Text91.Value = temp4
Text95.Value = temp5
Text93.Value = temp6
Text103.Value = temp7
Text105.Value = temp8
Text99.Value = temp9
Text101.Value = temp10
End Sub
Private Sub Text6_Click()
Text6 = vbNullString
End Sub