Code will not compile

J

Jan Il

Hi all - Access 2002, Windows ME

I am having a time trying to figure out why this code keeps causing the
debugger to balk on compile. The part that keeps being highlighted is the
..EquipID of the code below. Everything else seems to be fine this is the
only thing in the db that causes the compile to object. I have redone this
and reviewed it and gone over the Help files, but, I just can't see where
the problem is.
I have tried changing the Me. to Me!, but, it makes no difference. I have
checked the form, checked the table and the query and all, and everything
seems correct.

I would truly appreciate it if someone would point out where I have made the
mistake in this code.

Regards,
Jan :)

*****************Start of Code************************
Private Sub SaveRec_Click()
On Error GoTo Err_SaveRec_Click

If DCount("*", "[SignalCaseInspTbl]", "[EquipID]=""" & Me.cmbEquipID & """
And [Qtrly]=#" & Me.Qtrly & "#") = 0 Then

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer7

DoCmd.GoToRecord , , acNewRec

Me![cmbEquipID].SetFocus

Else

MsgBox "This Record Already Exists", vbExclamation, "Record Already
Exists"

End If

Exit_SaveRec_Click:
Exit Sub

Err_SaveRec_Click:
MsgBox Err.Description
Resume Exit_SaveRec_Click

End Sub
***********************************End Code***************************
 
J

John Vinson

If DCount("*", "[SignalCaseInspTbl]", "[EquipID]=""" & Me.cmbEquipID & """
And [Qtrly]=#" & Me.Qtrly & "#") = 0 Then
Click to expand...

I presume this is all on one line in the code (and that posting the
message caused the line break). What is the datatype of EquipID? Text?
If it's of Number type you don't need the extra quotes. And what's the
RowSource and Bound Column of cmbEquipID?
 
J

Jan Il

Hi John!
If DCount("*", "[SignalCaseInspTbl]", "[EquipID]=""" & Me.cmbEquipID & """
And [Qtrly]=#" & Me.Qtrly & "#") = 0 Then
Click to expand...
Click to expand...
I presume this is all on one line in the code (and that posting the
message caused the line break). What is the datatype of EquipID? Text?
If it's of Number type you don't need the extra quotes. And what's the
RowSource and Bound Column of cmbEquipID?
Click to expand...

Yes, it is all on one line, the break is due to reader wrap. The datatype
is text, and the RowSource of the cmbEquipID is
SELECT [SigCaseID Query].[EquipID] FROM [SigCaseID Query]

The SQL of the SigCaseID Query is:

SELECT DISTINCT [Signal Case Inventory Table].EquipID
FROM [Signal Case Inventory Table]
ORDER BY [Signal Case Inventory Table].EquipID DESC;

(Yes....I know..spaces = NoNo. It's old <g>)

Thank you for your time and assistance, I really appreciate it.

Regards,
Jan :)
 
J

Jan Il

Hi Andy!
Hi Jan

You've two references to cmbEquipID in your code, in the DCount line and
then in .SetFocus line. Which one is causing the problem and what is the
compiler error?

Things I'd check are (in this order);
a) control name is actually called 'cmbEquipID' and not something that you
are *reading* as cmbEquipID (because that's what it *should* be.. eg
cnbEquipID, cmbEqipID etc)
b) this control is actually on the current Form
c) rem out the DCount line and the lines Else to End If and see if the
compiler gives the same problem at .SetFocus line
d) Try single quotes around Me.cmbEquipID in the DLookUp;
If ....."[EquipID]='" & Me.cmbEquipID & "' And [Qtrly]=#" ....
e) Try Chr(34) in the DLookup;
If ....."[EquipID]=" & Chr(34) & Me.cmbEquipID & Chr(34) & " And
[Qtrly]=#" ....

HTH

Andy
Click to expand...

The Compile error I got was: Method or data member not found

I tried the changes to the code that you suggeted, and when I changed
it to the e) selection in your list it worked perfectly! All compiled
without objection. I also made a change in the placement of the
MsgBox part, so the Save button is working as it should. I had
never used the Chr() thingie before, so, this was a new lesson for
me as well. ;-]]

Thank you very much for your time and additional input, Andy, I really
do appreciate it.

Regards,
Jan :)

Jan Il said:
Hi all - Access 2002, Windows ME

I am having a time trying to figure out why this code keeps causing the
debugger to balk on compile. The part that keeps being highlighted is the
.EquipID of the code below. Everything else seems to be fine this is the
only thing in the db that causes the compile to object. I have redone this
and reviewed it and gone over the Help files, but, I just can't see where
the problem is.
I have tried changing the Me. to Me!, but, it makes no difference. I have
checked the form, checked the table and the query and all, and everything
seems correct.

I would truly appreciate it if someone would point out where I have made the
mistake in this code.

Regards,
Jan :)

*****************Start of Code************************
Private Sub SaveRec_Click()
On Error GoTo Err_SaveRec_Click

If DCount("*", "[SignalCaseInspTbl]", "[EquipID]=""" & Me.cmbEquipID & """
And [Qtrly]=#" & Me.Qtrly & "#") = 0 Then

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer7

DoCmd.GoToRecord , , acNewRec

Me![cmbEquipID].SetFocus

Else

MsgBox "This Record Already Exists", vbExclamation, "Record Already
Exists"

End If

Exit_SaveRec_Click:
Exit Sub

Err_SaveRec_Click:
MsgBox Err.Description
Resume Exit_SaveRec_Click

End Sub
***********************************End Code***************************
Click to expand...
Click to expand...
 
A

Andy Cole

Hi Jan

Glad you've got it working. Weird though, I guess A2K2 gets as confused
about multiple double-quotes as I do at times!!

Andy

Jan Il said:
Hi Andy!
Hi Jan

You've two references to cmbEquipID in your code, in the DCount line and
then in .SetFocus line. Which one is causing the problem and what is the
compiler error?

Things I'd check are (in this order);
a) control name is actually called 'cmbEquipID' and not something that you
are *reading* as cmbEquipID (because that's what it *should* be.. eg
cnbEquipID, cmbEqipID etc)
b) this control is actually on the current Form
c) rem out the DCount line and the lines Else to End If and see if the
compiler gives the same problem at .SetFocus line
d) Try single quotes around Me.cmbEquipID in the DLookUp;
If ....."[EquipID]='" & Me.cmbEquipID & "' And [Qtrly]=#" ....
e) Try Chr(34) in the DLookup;
If ....."[EquipID]=" & Chr(34) & Me.cmbEquipID & Chr(34) & " And
[Qtrly]=#" ....

HTH

Andy
Click to expand...

The Compile error I got was: Method or data member not found

I tried the changes to the code that you suggeted, and when I changed
it to the e) selection in your list it worked perfectly! All compiled
without objection. I also made a change in the placement of the
MsgBox part, so the Save button is working as it should. I had
never used the Chr() thingie before, so, this was a new lesson for
me as well. ;-]]

Thank you very much for your time and additional input, Andy, I really
do appreciate it.

Regards,
Jan :)

Jan Il said:
Hi all - Access 2002, Windows ME

I am having a time trying to figure out why this code keeps causing the
debugger to balk on compile. The part that keeps being highlighted is the
.EquipID of the code below. Everything else seems to be fine this is the
only thing in the db that causes the compile to object. I have redone this
and reviewed it and gone over the Help files, but, I just can't see where
the problem is.
I have tried changing the Me. to Me!, but, it makes no difference. I have
checked the form, checked the table and the query and all, and everything
seems correct.

I would truly appreciate it if someone would point out where I have
Click to expand...
made
the
mistake in this code.

Regards,
Jan :)

*****************Start of Code************************
Private Sub SaveRec_Click()
On Error GoTo Err_SaveRec_Click

If DCount("*", "[SignalCaseInspTbl]", "[EquipID]=""" & Me.cmbEquipID & """
And [Qtrly]=#" & Me.Qtrly & "#") = 0 Then

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer7

DoCmd.GoToRecord , , acNewRec

Me![cmbEquipID].SetFocus

Else

MsgBox "This Record Already Exists", vbExclamation, "Record Already
Exists"

End If

Exit_SaveRec_Click:
Exit Sub

Err_SaveRec_Click:
MsgBox Err.Description
Resume Exit_SaveRec_Click

End Sub
***********************************End Code***************************
Click to expand...
Click to expand...
Click to expand...
 
J

Jan Il

Hi Andy,

I am finding out fast since my recent upgrade to A2K2, that being 'weird' is
one of the things it seems to like to do best for me. :)

Thanks again for your help,

Jan :)

Andy Cole said:
Hi Jan

Glad you've got it working. Weird though, I guess A2K2 gets as confused
about multiple double-quotes as I do at times!!

Andy

Jan Il said:
Hi Andy!
Hi Jan

You've two references to cmbEquipID in your code, in the DCount line and
then in .SetFocus line. Which one is causing the problem and what is the
compiler error?

Things I'd check are (in this order);
a) control name is actually called 'cmbEquipID' and not something that you
are *reading* as cmbEquipID (because that's what it *should* be.. eg
cnbEquipID, cmbEqipID etc)
b) this control is actually on the current Form
c) rem out the DCount line and the lines Else to End If and see if the
compiler gives the same problem at .SetFocus line
d) Try single quotes around Me.cmbEquipID in the DLookUp;
If ....."[EquipID]='" & Me.cmbEquipID & "' And [Qtrly]=#" ....
e) Try Chr(34) in the DLookup;
If ....."[EquipID]=" & Chr(34) & Me.cmbEquipID & Chr(34) & " And
[Qtrly]=#" ....

HTH

Andy
Click to expand...

The Compile error I got was: Method or data member not found

I tried the changes to the code that you suggeted, and when I changed
it to the e) selection in your list it worked perfectly! All compiled
without objection. I also made a change in the placement of the
MsgBox part, so the Save button is working as it should. I had
never used the Chr() thingie before, so, this was a new lesson for
me as well. ;-]]

Thank you very much for your time and additional input, Andy, I really
do appreciate it.

Regards,
Jan :)

Hi all - Access 2002, Windows ME

I am having a time trying to figure out why this code keeps causing the
debugger to balk on compile. The part that keeps being highlighted
Click to expand...
is
the
.EquipID of the code below. Everything else seems to be fine this
Click to expand...
is
the
only thing in the db that causes the compile to object. I have
Click to expand...
redone
this
and reviewed it and gone over the Help files, but, I just can't see where
the problem is.
I have tried changing the Me. to Me!, but, it makes no difference. I have
checked the form, checked the table and the query and all, and everything
seems correct.

I would truly appreciate it if someone would point out where I have made
the
mistake in this code.

Regards,
Jan :)

*****************Start of Code************************
Private Sub SaveRec_Click()
On Error GoTo Err_SaveRec_Click

If DCount("*", "[SignalCaseInspTbl]", "[EquipID]=""" & Me.cmbEquipID
Click to expand...
&
"""
And [Qtrly]=#" & Me.Qtrly & "#") = 0 Then

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer7

DoCmd.GoToRecord , , acNewRec

Me![cmbEquipID].SetFocus

Else

MsgBox "This Record Already Exists", vbExclamation, "Record Already
Exists"

End If

Exit_SaveRec_Click:
Exit Sub

Err_SaveRec_Click:
MsgBox Err.Description
Resume Exit_SaveRec_Click

End Sub
***********************************End Code***************************
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Working outlook via vba code with xlsx - error 0
Help needed with Duplicate Check subroutine 4
Run time error '2001' You cancelled the previous operation 2
Compile Error 11
Compile Error:Variable not defined 4
compile error 1
Compile error: Argument not optional 3
Compile Database Code Problem 9

Top