DoCmd.OpenForm from selection in Listbox not Primary Key

[Dave370]

Hi,
I have 2 Tables and 3 Forms. 1st table is called Invoices and second table
is called Payments In. Both have primary keys and Payments In has a field
which is linked to the Invoice Primary Key. For both tables I have created
forms with listboxes which have a list of the records from their
corresponding tables. For the Invoice Listbox form I have a command button
which executes the following code when clicked:

Private Sub openinvoices_Click()
On Error GoTo Err_openinvoices_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Invoice"

stLinkCriteria = "[ID]=" & Me![List0].[Invoice ID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_openinvoices_Click:
Exit Sub

Err_openinvoices_Click:
MsgBox Err.Description
Resume Exit_openinvoices_Click

End Sub

This opens up another form where you can edit information about the Invoice.
How would I do the same for the Payments In Listbox form and make it open up
an Invoice in the Invoice form even though the field to link to the correct
Invoice is not a Primary Key?

 

[Beetle]

It doesn't have to be a primary key to be used as criteria to open a form.
Just include the Invoice Id field from the Payments In table in the row source
of your Payments list box. Make it 0" width (hidden) if you don't want the
users to
see it, then refer to it using the column method like this;

stLinkCriteria = "[ID]=" & Me![YourListBox].Column(x)
DoCmd.OpenForm stDocName, , , stLinkCriteria

where x repesents the numeric value of whatever column it happens to be
in your list box. Keep in mind that it's a zero based numbering system, so the
first column is Column(0), second is Column(1), etc.
_________

Sean Bailey

 

[Dave370]

Thanks, that worked.