UtterAccess Discussion Forums > Opening another form on basis of two values

Full Version: Opening another form on basis of two values

UtterAccess Discussion Forums > Microsoft® Access > Access Forms

muamshai

Mar 17 2005, 06:23 AM

Hello,
I have a forum which will take two inputs from person and will open the form.
The table is like this

CODE

PERSON_ID      VISIT_ID
1001                     1
1001                     2
1002                     1
1002                     2

When the data entry operator will give the two values, she will have another form open.
The form, which is taking these two values, has the following code.

CODE

Dim strvalue As Long
Dim strvalue1 As Long
strvalue = Me.Text0.Value
strvalue1 = Me.Text1.Value
If IsNull(DLookup("PERSON_ID", "Table", "PERSON_ID = " & strvalue & "VISIT_NO = " & strvalue1)) Then
    MsgBox "Number is not there", vbCritical + vbOKOnly, "DPS Neuropatia"
Else
    DoCmd.Close
    DoCmd.OpenForm "Entry_form", acNormal, , _
            "PERSON_ID = " & strvalue And "VISIT_NO = " & strvalue1
End If

I am making a mistake in syntax but I don't know where. Can somebody please help me?

Thanks

jsitraining

Mar 17 2005, 06:28 AM

So Close

Try

"PERSON_ID = " & strvalue & " And VISIT_NO = " & strvalue1

A couple of pointers in naming tho.
You have declared strValue and strValue1 as Longs, this is fine as presumably they are numbers, however you have prefixed the variabl name with str which implies that they are Strings (str = String)
I would suggest - simply as better practice to prefix the Variable name with lng for Long giving you
lngValue and lngValue1

Other common prefixes:

Datatype Prefix
Double dbl
Integer int
Byte byt
Date dte

It can often help with the readability of yor code.

HTH
Jim

muamshai

Mar 17 2005, 06:35 AM

jsitraining,

thanks for your reply. Your hint worked.

However, now I am facing problem with these lines:

CODE

DoCmd.OpenForm "Entry_form", acNormal, , _
"PERSON_ID = " & lngvalue And "VISIT_NO = " & lngvalue1

should And be in the quotes?

jsitraining

Mar 17 2005, 06:37 AM

Yes, the AND is part of the SQL retriction that you are building and as such must be part of the string (in Quotes. You are simply concatenating the Variables into the overall SQL String. Check my prvious answer

Jim

muamshai

Mar 17 2005, 06:41 AM

oh yes! Thanks a lot. Seems that I need a few hours sleep

But..Homer Simpson rocks!

jsitraining

Mar 17 2005, 06:42 AM

LOL, No probs.
Jim

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.