If / then question

[alpine82]

I have code that uses the value of a field on a form to lookup a record inn another table. After the lookup is done, I want it to use and if/then statement to tell me if the box in the record is Null or has a value, and if it has a value I want it to open a form. Any ideas. I was thinking something like this.

Dim db As DAO.Database
Dim ItemFile As DAO.Recordset
Dim look As String

Set db = CurrentDb()
Set ItemFile = db.OpenRecordset("Item File")
look = Me!ItemNumber

If [ItemFile]![CardPassNum] = true Then
DoCmd.OpenForm("Form",acNormal)

On the last line I get a comiler error though, says illegal '=' for the DoCmd.
Any ideas?

 

[dcx693]

You don't need the parentheses around DoCmd.OpenForm("Form",acNormal), just use this: DoCmd.OpenForm "Form",acNormal

What's the purpose of this line:
look = Me!ItemNumber

Also, you need an End If statement after the DoCmd.OpenForm line.

If you want to test for Nulls, instead of:
If [ItemFile]![CardPassNum] = true
You can try:

If IsNull([ItemFile]![CardPassNum]) Then
  'do this
Else
  'do this
End If

 

[alpine82]

the line ' look = Me!ItemNumber '

I am trying to lookup what was entered in the text box ItemNumber on the form, in the table Item File which is the recordset. It doesn't work though.

 

[jfgambit]

Although not recommended, I believe you are looking for a DLookUp...

Dim rsItm as Variant

rsItm = DLookUp("[CardPassNum]","ItemFile","[ItemNumber] = " & Me.Look)

If IsNull(rsItm) or rsItm = "" then
Msgbox "Null Value in Card Pass Number"
Else
Docmd.OpenForm "FormName"
End if

 

[Pat Hartman]

You need to use the item number in the where argument of the OpenForm method. That is how Access knows what record to open the form to. You don't need the "look" variable and I don't know what the DAO code is for either. You are not doing anyting with the recordset that you opened. Get rid of it.

If [ItemFile]![CardPassNum] = true Then
DoCmd.OpenForm "Form", acNormal, , , "ItemNumber = " & Me.ItemNumber
End If

 

[alpine82]

I get a data type mismatch. I don't know why.

 

[jfgambit]

Try:

If [ItemFile]![CardPassNum] = true Then
DoCmd.OpenForm "Form", acNormal, , , "ItemNumber = '" & Me.ItemNumber & "'"
End If

This will work if your data type for the ItemNumber is text.

Of course, why would you name a Text field xxxxxnumber??

 

[alpine82]

The data type in the table is text. Is there a way to set the data type on a text box. I have never heard of it. Anyway i still get the data mismatch error.

 

[Mile-O]

What's an example of a typical ItemNumber?

 

[Pat Hartman]

Does ItemNumber contain single quotes? If so, you need to use the double quote as the string delimiter:

DoCmd.OpenForm "Form", acNormal, , , "ItemNumber = " & chr(034) & Me.ItemNumber & chr(034)

 

[Mile-O]

Or:

DoCmd.OpenForm "Form", acNormal, , , "ItemNumber = """ & Me.ItemNumber & """"