Open Form Criteria Question

  • Thread starter Thread starter Ken
  • Start date Start date
K

Ken

Hi and Thanks in advance for looking at this

It would be helpful if I could open a form using a string
criteria. However, I have been unsuccessful in my efforts
so far. I have tried using the help files but I invariably
get one of two errors when I run the code. Access either
rejects the criteria and gives an Invalid Use of Null
error, or it evaluates the criteria and decides it is a
parameter and invokes a request for the user to enter the
parameter. I'm sure I'm not doing something right...please
help.

Public MyString as String
MyField = TextBox
Form is based on query
Test are run to validate data prior to opening the form

MyString = MyField.Value

Docmd.OpenForm "MyForm",,,"[StringField]='" & MyString &_
"'",,acWindowNormal

Can this be done? Or must it be done in some other way?
 
Hi, Ken.

I think the following code does what you want. Attached
to a command button on my Manufacturer's data entry form,
it opens up a form displaying the local distributor.

Private Sub cmdOpenDistributorForm_Click()
On Error GoTo Err_cmdOpenDistributorForm_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmDistributors_Reps"

stLinkCriteria = "[DistID]=" & Me![cboDistName]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdOpenDistributorForm_Click:
Exit Sub

Err_cmdOpenDistributorForm_Click:
MsgBox Err.Description
Resume Exit_cmdOpenDistributorForm_Click

End Sub

HTH
Kevin Sprinkel
 
Public MyString as String
MyField = TextBox
Form is based on query
Test are run to validate data prior to opening the form

MyString = MyField.Value

Docmd.OpenForm "MyForm",,,,,acWindowNormal

Can this be done? Or must it be done in some other way?
Click to expand...
In VBA:
If this is for a jet engine you need double apostrophes.
"[StringField]=""" & MyString & """"
If this is for a sql server you need single apostrophes.
"[StringField]=''" & MyString & "''"

hth
Marc
 
Back
Top