Open Form via Link Criteria using unbound column of ComboBox

[112263]

Hello,

I have an open form on which there is a Command Button that is intended to open another form and show only records that match a field from the open form and an unbound column in a combo box of the form to be opened.

The result is that the form opens showing all records and in this latest (of many trials) version indicates:

---"Undefined Function '[Forms]![frmReturnDetails]![Combo24].Column' in expression."

Other trials yield the "Type Mismatch" Error. Thanks for your help / time.

Here is my code for the Command Button:

Private Sub Command9__OPEN_frmReturnDetails_Click()
On Error GoTo Err_Command9_Click

Dim stDocName As String
stDocName = "frmReturnDetails"

stLinkCriteria = "[Forms]![frmReturnDetails]![Combo24].Column(2)= ""& Me.OEM_ID & ""


DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command9_Click:

Exit Sub
Err_Command9_Click:
MsgBox Err.Description
Resume Exit_Command9_Click

End Sub

 

[Trevor G]

How many columns do you have in the Combo Box, as the first Column gets numbered as 0 (Zero) so would it be possible the you want Column(1) rather than Column(2)

 

[vbaInet]

Please read through this link:

http://baldyweb.com/wherecondition.htm

Your syntax is off.

 

[112263]

How many columns do you have in the Combo Box, as the first Column gets numbered as 0 (Zero) so would it be possible the you want Column(1) rather than Column(2)

There are 4 columns of which number 3 contains the field OEM_ID which I need to Link with.

Column numbering starts with "0"...

Thanks, Trevor

 

[112263]

Please read through this link:

Your syntax is off.

I will have to check later tonight asmy company internet settings block access to this URL.

Thank you.

 

[vbaInet]

Alright, here's what it reads:

---------------------------------------------------------------------
Often you might have a form or control that displays summary information, and you want to select one of those items and open a second form that displays the detailed information. The technique I use most often is the wherecondition argument of

DoCmd.OpenForm ([FONT=Arial]or [/FONT]DoCmd.OpenReport)

. It is sort of like saying "open this form but only show the record(s) that meets this criteria". Using this technique, the second form can be based on the table itself or a query that returns all records. It looks like this for a numeric value:

DoCmd.OpenForm "SecondFormName", , , "FieldName = " & Me.ControlName

Where SecondFormName is the name of the form being opened, FieldName is the field in that form's recordsource the restriction is based on, and ControlName is the name of the control on the current form that contains the value to be shown on the second form. As you will find throughout VBA, text and date values are treated differently. For text:

DoCmd.OpenForm "SecondFormName", , , "FieldName = '" & Me.ControlName & "'"

'(note, that's a double quote, single quote and double quote at the end)
For a date value, use # instead of the single quote:

DoCmd.OpenForm "SecondFormName", , , "FieldName = [COLOR=#ff0000]#[/COLOR]" & Me.ControlName[COLOR=#ff0000] & "#"[/COLOR]

As noted above, this technique can also be used when opening reports.