Find a specific record problem

[Steven811]

Hi

On a form(withsubforms) I have created a cmd button to open a second form and display the full details from a table that relates to a particular field. I wish to use the field [company] in the original form [frmJobTracking] as the criterion for the record search in [tblSupplier] and display the result in the form [frmSupplier].

The wizard has created the following which almost works. It opens the form and filters the records but, doesn't show any records. I think that I may have to use the original text field as a string search, I just don't know how to modify my code to do it.

Could anyone help?

Private Sub cmdFindSupplier_Click()
On Error GoTo Err_cmdFindSupplier_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "forSupplier"

stLinkCriteria = "[Company]=" & "'" & Me![cboSupplier] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdSupplier_Click:
Exit Sub

Err_cmdFindSupplier_Click:
MsgBox Err.Description
Resume Exit_cmdSupplier_Click

End Sub

Thanks in advance.

Steven811

 

[Oldsoftboss]

The Wizard created:

stLinkCriteria = "[ClientID]=" & Me![ClientID]

ClientID is an autonumber

and

stLinkCriteria = "[ClientAddress]=" & "'" & Me![ClientAddress] & "'"

ClientAddress is a text field.

HTH

Dave

 

[Steven811]

stLinkCriteria

The Wizard created:

stLinkCriteria = "[ClientID]=" & Me![ClientID]

stLinkCriteria = "[ClientAddress]=" & "'" & Me![ClientAddress] & "'"

Hi Dave

Thanks for helping

As you can tell I am a complete beginner.

In my code I have 1 instance of stLinkCriteria and I don't recognise [cboSupplier]:

stLinkCriteria = "[Company]=" & "'" & Me![cboSupplier] & "'"

I can't see the linked fields that you see, I realise that may because they are taken as given in the blanks and commas. For me to edit the code I would like to be able to identify which bits relate to them.

I would be very grateful for any assistance.

Thanks

Steven811

 

[Oldsoftboss]

You need to identify which fields on each form are common. They are usually the autonumber field. (Open the table in design view and see if any of the fields datatype is set to autonumber) The autonumber field automatically creates a unique number. If there is no autonumber field, create one, it makes referencing the table easier. If there is an autonumber field, note its name and go back to your form (forms) and see if the field is there, if not add it, then set the criteria to the field name.

Hope you follow.

Dave

PS: in the line

stLinkCriteria = "[Company]=" & "'" & Me![cboSupplier] & "'"

cboSupplier would probably refer to a combo box field.

 

[Steven811]

Created a new table and made [Company] a primaryID

Hi Dave

I've converted the field to a 'no duplicates' primary key field in a new table and moved the test data over.

The cbo box is the record selector on the form and uses the following SELECT statement:

SELECT [tblSupplier].[SuppKey], [tblSupplier].[Company] FROM tblSupplier

To my uninitiated eye this looks ok.

However the form when opened fails to show the record.

I have attached a copy if you would be kind enough to have a look.

Gratefully yours

Steven811

I have del most of the test data to get it to sensible size. The form is called forJT and the cmd button is the 1st button to right of the 1st supplier field.

 

[Oldsoftboss]

Not sure if I'm on the right track. I labeled the button I think you are talking about. I added the SuppKey to your popup supplier form and used that as the criteria. Look at the combo box row source, bound column and column widths. The bound column is the one you are using to reference when perfoming lookups etc.

One other thing you need to do is to compile you code on a regular basis. In the VB window click Debug-Compile from the menu.

I commented out the lines that wouldn't compile for me.

Dave

 

[Steven811]

Stunning

Hi Dave

That is truly a great help.

My understanding of what happens has increased exponentially.

You have no idea how much that has helped me.

Thanks

Steven811

 

[Oldsoftboss]

My understanding of what happens has increased exponentially.

exponentially

 

[Steven811]

exponentially

You have no idea just what a low base I started from.