Combo.Value in Access 97

[S Jackson]

I have this code in my db designed in Access 2000 that works great:

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmMaster"

stLinkCriteria = "[DHSAttny]=" & "'" & Me![Combo2] & "'"

If Me.Combo2.Value > 0 Then
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.Close acForm, "fdlgAttnyPickOpen"
Else
MsgBox "Please select an attorney and try again."
End If

But, when I convert the db to Access 97, the code will not run. If I leave
the combo box blank, it will display the message box, however, when I make a
selection, the code just sits there and does nothing. On other forms in the
97 db with the same code, I get this error: "Type mismatch." Is it the
stLinkCriteria ?

I have run out of ideas on how to make this work. Any help is very, very
much appreciated as I am working on a deadline of 5:00 p.m.

Thanks
S. Jackson

 

[fredg]

I have this code in my db designed in Access 2000 that works great:

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmMaster"

stLinkCriteria = "[DHSAttny]=" & "'" & Me![Combo2] & "'"

If Me.Combo2.Value > 0 Then
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.Close acForm, "fdlgAttnyPickOpen"
Else
MsgBox "Please select an attorney and try again."
End If

But, when I convert the db to Access 97, the code will not run. If I leave
the combo box blank, it will display the message box, however, when I make a
selection, the code just sits there and does nothing. On other forms in the
97 db with the same code, I get this error: "Type mismatch." Is it the
stLinkCriteria ?

I have run out of ideas on how to make this work. Any help is very, very
much appreciated as I am working on a deadline of 5:00 p.m.

Thanks
S. Jackson
Regarding:
stLinkCriteria = "[DHSAttny]=" & "'" & Me![Combo2] & "'"<

As far as I can see, this should not work in any version of access!!
You have an extra " in the code.
stLinkCriteria = "[DHSAttny]= '" & Me![Combo2] & "'"
would have been OK. However it will fail if there is a name with an
apostrophe (i.e. O'Brien).

What is a actual DataType of the Bound column of Combo2?
Is it the Name of the Attorney (Text), or is the Bound column the
AttorneyID (Number)?

If the Bound Column is Text, use:
Dim stLinkCriteria as String
stLinkCriteria = "[DHSAttny]=" & chr(34) & Me![Combo2] & chr(34)

This has the advantage of accepting names with an apostrophe (i.e.
O'Connor) without erroring out.

If it is Number datatype, use:
Dim stLinkCriteria as Long
stLinkCriteria = "[DHSAttny]=" & Me![Combo2]

You can also save a bit of coding by using:

DoCmd.OpenForm stDocName, , , "[DHSAttny]=" & chr(34) & Me![Combo2] &
chr(34)
No need for stLinkCriteria at all.

Hope this has helped.

 

[S Jackson]

Fred, thank you so much. I am pretty green at all of this. I have some
books (Susann Novalis, Access 2000 VBA Handbook) to help me better
understand what I am doing. Unfortunately, the powers that be expect me to
do this without training or time to study (argh!). Thank you again.

S. Jackson

I have this code in my db designed in Access 2000 that works great:

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmMaster"

stLinkCriteria = "[DHSAttny]=" & "'" & Me![Combo2] & "'"

If Me.Combo2.Value > 0 Then
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.Close acForm, "fdlgAttnyPickOpen"
Else
MsgBox "Please select an attorney and try again."
End If

But, when I convert the db to Access 97, the code will not run. If I leave
the combo box blank, it will display the message box, however, when I make a
selection, the code just sits there and does nothing. On other forms in the
97 db with the same code, I get this error: "Type mismatch." Is it the
stLinkCriteria ?

I have run out of ideas on how to make this work. Any help is very, very
much appreciated as I am working on a deadline of 5:00 p.m.

Thanks
S. Jackson
Regarding:
stLinkCriteria = "[DHSAttny]=" & "'" & Me![Combo2] & "'"<

As far as I can see, this should not work in any version of access!!
You have an extra " in the code.
stLinkCriteria = "[DHSAttny]= '" & Me![Combo2] & "'"
would have been OK. However it will fail if there is a name with an
apostrophe (i.e. O'Brien).

What is a actual DataType of the Bound column of Combo2?
Is it the Name of the Attorney (Text), or is the Bound column the
AttorneyID (Number)?

If the Bound Column is Text, use:
Dim stLinkCriteria as String
stLinkCriteria = "[DHSAttny]=" & chr(34) & Me![Combo2] & chr(34)

This has the advantage of accepting names with an apostrophe (i.e.
O'Connor) without erroring out.

If it is Number datatype, use:
Dim stLinkCriteria as Long
stLinkCriteria = "[DHSAttny]=" & Me![Combo2]

You can also save a bit of coding by using:

DoCmd.OpenForm stDocName, , , "[DHSAttny]=" & chr(34) & Me![Combo2] &
chr(34)
No need for stLinkCriteria at all.

Hope this has helped.

 

[S Jackson]

Fred:

I tried the code. If I take out the If statement, it works fine. But when
I put in the following If statement I get tye Type mismatch error:

If Me.Combo2.Value > 0 Then

The Combo box has one bound column and it is text.

Any thoughts?
S. Jackson

I have this code in my db designed in Access 2000 that works great:

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmMaster"

stLinkCriteria = "[DHSAttny]=" & "'" & Me![Combo2] & "'"

If Me.Combo2.Value > 0 Then
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.Close acForm, "fdlgAttnyPickOpen"
Else
MsgBox "Please select an attorney and try again."
End If

But, when I convert the db to Access 97, the code will not run. If I leave
the combo box blank, it will display the message box, however, when I make a
selection, the code just sits there and does nothing. On other forms in the
97 db with the same code, I get this error: "Type mismatch." Is it the
stLinkCriteria ?

I have run out of ideas on how to make this work. Any help is very, very
much appreciated as I am working on a deadline of 5:00 p.m.

Thanks
S. Jackson
Regarding:
stLinkCriteria = "[DHSAttny]=" & "'" & Me![Combo2] & "'"<

As far as I can see, this should not work in any version of access!!
You have an extra " in the code.
stLinkCriteria = "[DHSAttny]= '" & Me![Combo2] & "'"
would have been OK. However it will fail if there is a name with an
apostrophe (i.e. O'Brien).

What is a actual DataType of the Bound column of Combo2?
Is it the Name of the Attorney (Text), or is the Bound column the
AttorneyID (Number)?

If the Bound Column is Text, use:
Dim stLinkCriteria as String
stLinkCriteria = "[DHSAttny]=" & chr(34) & Me![Combo2] & chr(34)

This has the advantage of accepting names with an apostrophe (i.e.
O'Connor) without erroring out.

If it is Number datatype, use:
Dim stLinkCriteria as Long
stLinkCriteria = "[DHSAttny]=" & Me![Combo2]

You can also save a bit of coding by using:

DoCmd.OpenForm stDocName, , , "[DHSAttny]=" & chr(34) & Me![Combo2] &
chr(34)
No need for stLinkCriteria at all.

Hope this has helped.

 

[S Jackson]

The fix is:

If IsNull(Me.Combo2} Then

Thanks for your help!
S.Jackson

Fred:

I tried the code. If I take out the If statement, it works fine. But when
I put in the following If statement I get tye Type mismatch error:

If Me.Combo2.Value > 0 Then

The Combo box has one bound column and it is text.

Any thoughts?
S. Jackson

I have this code in my db designed in Access 2000 that works great:

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmMaster"

stLinkCriteria = "[DHSAttny]=" & "'" & Me![Combo2] & "'"

If Me.Combo2.Value > 0 Then
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.Close acForm, "fdlgAttnyPickOpen"
Else
MsgBox "Please select an attorney and try again."
End If

But, when I convert the db to Access 97, the code will not run. If I leave
the combo box blank, it will display the message box, however, when I make a
selection, the code just sits there and does nothing. On other forms

in

the

97 db with the same code, I get this error: "Type mismatch." Is it the
stLinkCriteria ?

I have run out of ideas on how to make this work. Any help is very, very
much appreciated as I am working on a deadline of 5:00 p.m.

Thanks
S. Jackson
Regarding:
stLinkCriteria = "[DHSAttny]=" & "'" & Me![Combo2] & "'"<

As far as I can see, this should not work in any version of access!!
You have an extra " in the code.
stLinkCriteria = "[DHSAttny]= '" & Me![Combo2] & "'"
would have been OK. However it will fail if there is a name with an
apostrophe (i.e. O'Brien).

What is a actual DataType of the Bound column of Combo2?
Is it the Name of the Attorney (Text), or is the Bound column the
AttorneyID (Number)?

If the Bound Column is Text, use:
Dim stLinkCriteria as String
stLinkCriteria = "[DHSAttny]=" & chr(34) & Me![Combo2] & chr(34)

This has the advantage of accepting names with an apostrophe (i.e.
O'Connor) without erroring out.

If it is Number datatype, use:
Dim stLinkCriteria as Long
stLinkCriteria = "[DHSAttny]=" & Me![Combo2]

You can also save a bit of coding by using:

DoCmd.OpenForm stDocName, , , "[DHSAttny]=" & chr(34) & Me![Combo2] &
chr(34)
No need for stLinkCriteria at all.

Hope this has helped.