Lookup Tables

[moepower]

Out of necessity I have to create five lookup tables with only one field in each table. I prefer the users not have to open five different Form windows with only one field to enter lookup data.

Is it possible to have one Form but fields from all five lookup tables, the user can enter lookup information for only the fields they need and click a command button to save the information only for the fields the enter.

Is there a cleaner solution to this that I'm missing?

Thanks

 

[Telsa]

Yes, it is very possible to use the combo box to lookup info for several different tables... and then save the info in another table. That is what makes combo boxes cool!

Once you set the field as a combo box, you will see in its properties a Table/Query selection. Here is where you would select the table to lookup.

Then once the user selects from that table, it will be saved to the table your form is based on.

HTH

Mary

 

[moepower]

Hi Mary,

Thanks for the response. I am actually rying to find a way where I can create one Form window for the users to enter data into the the five different lookup tables themselves.

Because I have five lookup tables and each table has only one field. I prefer the user does not have to open each lookup table to enter the lookup information. ie. I want to have a "Lookup Form" where the user can enter lookup data for one or all five tables at once.

Sorry for not being clearer in the origional email.

Thanks,

 

[Telsa]

OH, then you want to create an UPDATE SQL statement for each table via VBA coding in the FOrm's module. That is my first thought. Maybe based on the click event of a button, to update all tables except if the field is empty.

Something like that??

Mary

 

[moepower]

I also found a section in Access Help where if you:

a. Set RowSourceType = Value List
b. LimitToList = Yes
c. Use the OnNotInList event with the following procedure

----
Private Sub NameField_NotInList(NewData As String, Response As Integer)
Dim ctl As Control

' Return Control object that points to combo box.
Set ctl = Me!NameField
' Prompt user to verify they wish to add new value.
If MsgBox("Value is not in list. Add it?", vbOKCancel) = vbOK Then
' Set Response argument to indicate that data
' is being added.
Response = acDataErrAdded
' Add string in NewData argument to row source.
ctl.RowSource = ctl.RowSource & ";" & NewData
Else
' If user chooses Cancel, suppress error message
' and undo changes.
Response = acDataErrContinue
ctl.Undo
End If
End Sub

---

Could you modify this concept so that it will work with RowSourceType = Table/Query? I don't know enough VBA to change the Event Procedure logic.

Thanks,