Form with foreign keys (1 Viewer)

Swedude

Registered User.
Local time
Today, 11:21
Joined
Jan 12, 2010
Messages
13
Been trying to find info about this but it's hard to find the exact question I have, especially since, being in Sweden, the program and help file is in Swedish.

I'm using Access 2007 and I'm supposed to make a database. What I'm wondering is the following: I have a bunch of tables such as Customer and Customer Group.

Customer contains 2 fields whose data are to be manually entered, no problems there. But the other 4 are foreign keys from other classes such as Customer Group and Seller.

Having these fields show up as Customer GroupID, SellerID etc. is not what I need. They need to display the choices actually stored in those classes so that in the Customer form, you choose what to put in those fields from a drop-down list.

How do you set it up like that? The relations are already in place.
 

dcb

Normally Lost
Local time
Today, 12:21
Joined
Sep 15, 2009
Messages
529
Create a query with both the "main table" and other tables in :

So where you have each foreign key replace that with the field from the FK Table that you wish to display


Alternativly use combo boxes
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 03:21
Joined
Aug 30, 2003
Messages
36,118
The normal setup would be a combo box on the form that returned the ID field and the text value. The ID field would be the bound column and is what would be stored in the related tables. That column would be hidden, so the user would only see the text value.
 

Swedude

Registered User.
Local time
Today, 11:21
Joined
Jan 12, 2010
Messages
13
So I'd need the combo box instead of the list box because the field is a foreign key? I take it if it were a "table-internal" field, a list box would suffice. Not that I know how to put in either.
 

dcb

Normally Lost
Local time
Today, 12:21
Joined
Sep 15, 2009
Messages
529
I'll leave this one for Paul - he is great with the combo-boxes
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 03:21
Joined
Aug 30, 2003
Messages
36,118
I'll leave this one for Paul - he is great with the combo-boxes

My sarcasm detector just went off! :p

dcb and I interpreted the question differently, I think. If you're talking about displaying the data on a report, then the query is the way to go. I though you meant on a data entry/edit form, in which case I'd use the combo. As to combo vs list box, it would depend on the look and functionality you wanted. Either one can do what I described.
 

Swedude

Registered User.
Local time
Today, 11:21
Joined
Jan 12, 2010
Messages
13
It's the latter. This is what 2 of the classes look like:

Customer (a company):
CustomerID - counter
Company name - text
Customer Group - number (foreign key)
Customer Type - number (fk)
Zip code - text (fk)
Adress - text
SellerID - number (fk)

Hopefully if I learn how to do this with one class, I can do it with all of them so here's a second class:

Seller
First name
Last name

So in the form for Customer, the Seller field should have a list of whatever sellers are registered there. Which might require another combo thingy, since the name is a combo of first and last? The other foreign key fields are to have lists as well, though they're simpler since their tables contain only one field each.
 

Swedude

Registered User.
Local time
Today, 11:21
Joined
Jan 12, 2010
Messages
13
Anyone? Kind of stuck until this problem is resolved. The form isn't very good with what amounts to four ID fields...
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 03:21
Joined
Aug 30, 2003
Messages
36,118
Not sure what you mean by classes. Are you using a class module? Presuming you have related tables, the combo is the way I would go.
 

Swedude

Registered User.
Local time
Today, 11:21
Joined
Jan 12, 2010
Messages
13
Tables, sorry. They're related, yep. How do I go about making the combo?
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 03:21
Joined
Aug 30, 2003
Messages
36,118
Try the combo wizard; it should step you through it, and it hides the key field by default anyway.
 

Swedude

Registered User.
Local time
Today, 11:21
Joined
Jan 12, 2010
Messages
13
Is that the option at the bottom for field data type? Stupid Swedish version. Edit: I used it and it seems to work better at least. But even though I include zip code and city, it looks like this: 000 00 | City in the drop-down list but when I choose it, the field just says 000 00.
 
Last edited:

pbaldy

Wino Moderator
Staff member
Local time
Today, 03:21
Joined
Aug 30, 2003
Messages
36,118
Hiding the key column? In US versions, it is a checkbox on the screen where it lets you size the columns, entitled "Hide key column (recommended)". Picture attached.
 

Attachments

  • Combo.JPG
    Combo.JPG
    33.3 KB · Views: 441

dcb

Normally Lost
Local time
Today, 12:21
Joined
Sep 15, 2009
Messages
529
My sarcasm detector just went off! :p

dcb and I interpreted the question differently, I think. If you're talking about displaying the data on a report, then the query is the way to go. I though you meant on a data entry/edit form, in which case I'd use the combo. As to combo vs list box, it would depend on the look and functionality you wanted. Either one can do what I described.

Indeed no sarcasm - I have seen a number of your posts on combo boxes and cascading etc - I appreciate the way you describe them and know when and how to lead the user to a appropriate method.... On the other hand I lean towards helping people with the "funny" vba problems etc so my mindset is often different - I dont think, even though this gives the end result, that this would help the OP whatsoever....
Code:
Public Sub CreateCombo()
    Dim ctlCmb As Control
    Dim frm As Form
    Dim strFormName As String: strFormName = "Form4"    '' Change Your Form Name Here!!!
    
    DoCmd.OpenForm strFormName, acDesign
    Set frm = Forms.Item(strFormName)
    Set ctlCmb = CreateControl(frm.Name, acComboBox, acDetail, , , , , 2000, 300)
    With ctlCmb
        .Name = "cmbSeller"
        .RowSource = "Select sellerID,[FirstName] & " & """ """ & " & [LastName] as FullName from tblSeller"
        .ColumnWidths = "0;3"
        .ColumnCount = 2
        .ControlSource = "SellerID"
    End With
    
    DoCmd.Close acForm, frm.Name, acSaveYes
End Sub
Swedude - Please dont try this at home.....:D
 

Users who are viewing this thread

Top Bottom