Relational Data using Forms 2

[bozic12]

I know I'm missing something fairly simple here...

I have a form controlled by a table which has a foreign key to another table. I would like the user to input a value into a text box, and if that value is in the other table, place that primary key (the foreign key in the controlling table) as the value for that attribute in the controlling table.

Main_Stackup_Table
Stackup_ID (autonumber) -- PK
Cutter_ID -- FK
Holder_ID -- FK
Size
Weight
.
.
.

Cutter_Table
Cutter_ID (autonumber) -- PK
Cutter_Name
Cutter_Size
Color
.
.
.

Holder_Table
Holder_ID (autonumber) -- PK
Holder_Name
Manufacturer
Holder_Weight
.
.
.

So, the form is controlled by the Main_Stackup_Table and I would like there to be text boxes for the user to input a Cutter Name and one for Holder Name. If these values exist in their respective tables, the record's Cutter_ID and Holder_ID fields in the Main_Stackup_Table would populate with the associated Foreign Key (The ID's of the respective tables).

These are 1:Many relationships -- There will only be one unique Cutter_ID/Holder_ID, but many Stackups can use the same cutter and/or holder.

Thanks for any help provided, it is much appreciated.

Jeff

 

[PHV]

Have you tried to play with ComboBox ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[TheAceMan1]

How are ya bozic12 . . .

Have a look at the following and things you ask should become clearer! . . . in fact . . . the heart & soul of any db lies here!

Fundamentals of Relational Database Design

Normalizing Tables

Table Relationships

See Ya! . . . . . .

 

[bozic12]

Thank you both for your replies.

PHV -- I would prefer not to use comboboxes, as some of these fields could contain thousands of records. In addition, I have a form that they can launch in which to search for a particular value if need be (typically, the user will know this value though, and will not need to search for it). Also, I tried the combobox wizard and was not able to find a solution to my dilema.

AceMan -- I appreciate the links given, although I have a solid understanding of relational databases in a theoretical sense. My tables are all in 3NF and my relationships are defined properly. I just don't have much experience with MS Access.

My issue isn't so much in the theory of designing the db, it is in the direct application of how to apply the relationships in a form. I pretty much defined my issue as clearly as I could in my initial posting. How do you use a relationship to populate a foreign key's value into a table when a value in the record of the matching value to that foreign key's value is inputted into a form controlled. I'm guessing it would be with a query/ies.

Again, much thanks to those who replied and those who can offer any help.

Jeff

 

[JoeAtWork]

I would still say that using a combo-box is your easiest solution - it's the one where you wouldn't have to write your own code. Although I think with thousands of records it would need to be Access2003, as earlier versions had a smaller limit on combo-lists (I believe 512 items). If you set LimitToList = True the auto-complete should make it easy for your users - they wouldn't need to pick from the list they could just type it in. It would also be self-validating (again, without you having to write any code).

If you are having trouble with the combo-box wizard just remember (for the Cutter_ID, for example):
1. User the Cutter_Table as the datasource for the combo
2. Choose the Cutter_ID and Cutter_Name fields
3. Sort by Cutter_Name
4. Hide the key column (as suggested by the wizard)
5. When it asks whether you want to "Remember the value for later use" or "Store that value in this field" choose the latter and choose "Cutter_ID"
6. Give it whatever label you like

But if you still want to do the textbox thing:

Place an unbound textbox on the form, name it txtCutterName. Place a button next to it, let's name it "btnSetCutterID". Place another textbox that is bound to the Cutter_ID foreign key (the easiest thing to do is drag it from the field list onto the form). Name it txtCutterID - possibly you would make this invisible as the ID probably doesn't mean anything to your users. In the Click event for btnSetCutterID, add the following code:

Private Sub btnSetCutterID_Click()
   dim lngCutterID as Long

   lngCutterID = nz(DLookup("Cutter_ID","Cutter_Table","Cutter_Name = '" & nz(txtCutterName,"") & "'"),0)

   If lngCutterID <> 0 Then
      txtCutterID = lngCutterID
   Else
      MsgBox "Error, the cutter name you have entered does not exist in the database."
   End If
End Sub

You make a similar set of controls and code for the Holder_ID.

If it were me, I would add a couple things to make it more user friendly. For one, in addition to (or instead of) the button I would add code to the KeyPress event of txtCutterName to monitor for the user pressing ENTER (Ascii = 13) and have it run the code above (saves the user pressing the button). I think I would also have an unbound and locked textbox to display the cutter name once it was looked up - just to reassure the user that it was entered correctly. I refrain from adding this to keep the example as simple as possible.

 

[TheAceMan1]

bozic12 . . .

Have a look at the [blue]DLookup[/blue] function. Here's an example with the code in the [blue]AfterUpdate[/blue] event of [blue]Cutter_Name[/blue]:

[blue]   Dim ID, Criteria As String
   Msg As String, Style As String, Title As String
   
   Criteria = "[Cutter_Name] = '" & Me![purple][b]TextboxName[/b][/purple] & "'"
   ID = DLookup("[Cutter_ID]", "Cutter_Table", Criteria)
   
   If IsNull(ID) Then
      Msg = "Cutter_ID Not Found!"
      Style = vbInformation + vbOKOnly
      Title = "No Cutter_ID Error! . . ."
      MsgBox Msg, Style, Title
   Else
      Me!Cutter_ID = ID
   End If[/blue]

See Ya! . . . . . .

 

[bozic12]

Cool,

Thank you both for your posts. I will try-out both methods.
Joe -- I like your method, and using code is no problem. I am actually (believe it or not from my post) quite familiar with VB (although I have not had exposure to the nz function - a handy little one after looking it up though!!). Seems like such a simple solution, but a creative one.
AceMan -- Your solution seems a bit simpler, although by just going over the code, I would like the textbox to still show the cutter name, and not show the associated ID.

Props to both of you, though. I was looking past such simple solutions, which tends to happen when you get caught up on something!!

Jeff