Populating a combo box....

[Daniel]

I have a database which is split into two forms - company 'a' form and
company 'b' form - within these two forms are different supplier names for
each company.

From time to time the same supplier will appear in both but with different
data (as each form is based on a different table)

What I'd like to do is when a command button is pressed, for the other form
to open and show the data based on the suppliers name - The main issue that
I'm coming across is how can I populate the combo box in the second form to
show the suppliers name from selecting it in the first form?

I use the combo box to search for suppliers, but would also like it to show
a suppliers name based on it being opened from another form.

Hope all that makes sense and thanks in advance

Daniel

 

[Tom Wickerath]

Hi Daniel,

I have a database which is split into two forms - company 'a' form and
company 'b' form - within these two forms are different supplier names for
each company.

From time to time the same supplier will appear in both but with different
data (as each form is based on a different table)

This is not considered a normalized database design. Instead, you should
have one table with a field added to identify the company. I suggest that you
read up on database design. Here is a link with many papers:

http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101

I suggest reading the first two papers, written by database design expert
Michael Hernandez, who is also the author of the book titled Database Design
for Mere Mortals.

You can use the optional WhereCondition parameter of the DoCmd.Openform
method to open FormB using FormA, filtered to a supplier shown in a combo box
in FormA, and vice versa. In this case, the name of the combo box is
"cboSuppliers". Something like this, for the Click Event code of a command
button:

Use this for text-based Supplier field:
DoCmd.Openform "FormB", _
WhereCondition:="[Supplier] = '" & Me.cboSuppliers & "'"

or
Use this for a numeric SupplierID field:

DoCmd.Openform "FormB", _
WhereCondition:="[SupplierID] = " & Me.cboSuppliers

...but would also like it to show
a suppliers name based on it being opened from another form.

Add a Form_Current event procedure to each form:

Private Sub Form_Current( )
On Error GoTo ProcError

' Synchronize name in combo box with displayed record.
cboSupplier = SupplierID

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume ExitProc
End Sub


See http://www.access.qbuilt.com/html/find_a_record.html for more
information on using a combo box to find a record.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________