Two fields make the key, select item on form

[ggreg]

How do I make a combo box that has two fields for the key
to select a record on the form?

the first field is A and the second field is B


I can alway get the right A selected but if there is a second
or third B to go with that A I can not get the record to come up
by select the choice in the combo box

How do I make the combo box to select a record on the
form went two fields make the primary Key?

 

[dcx693]

You can use a multicolumn combo box that displays two columns by contatenating the keys in the column that is displayed. Of course, the combo box should be unbound. You could also use two separate combo boxes that are linked (i.e., a cascading pair).

 

[WayneRyan]

ggreg,

The combo has two columns.

The rowsource for the combo:

Select a, b
From YourTable
Order ByA;

The SQL to find a record:

Select *
From SomeTable
Where FldA = '" & Me.YourCombo.Column(0) & "' And " & _
     "FldB = '" & Me.YourCombo.Column(1) & "'"

Wayne

 

[ggreg]

Thanks WayneRyan and dcx693,


WayneRyan,

putting code in...Private Sub Combo62_GotFocus()

Select * From tbljobs Where DES = '" & Me.YourCombo.Column(0) & "' And " & _
"TYPE = '" & Me.YourCombo.Column(1) & "'"

It does not like the Star....at the star it gives a compile error
expected Case

am I doing your code right?
dcx693,

before I posted here I tried using two combo box the first
one would set the second one but when i would make a selection
on the second one it would not go to DES I wanted it would
go to first case of type so it would not bring up right record.

 

[WayneRyan]

ggreg,

OK, now I think I see what you want to do.

What you need to do is make a query that gets all of the fields
from your table. Base your form on this query, not on the
table. It gets ALL of the records.

Now with combos cboA and cboB on your form add the following
to the criteria of your query.

KeyFieldA like "'*" & Forms![YourForm]![cboA] & "*'"
KeyFieldB like "'*" & Forms![YourForm]![cboB] & "*'"

or

KeyFieldA = "'" & Forms![YourForm]![cboA] & "'"
KeyFieldB = "'" & Forms![YourForm]![cboB] & "'"

You can trigger it with either (or both) the AfterUpdate event
of the combos. You can also set their default values to "*".

Wayne