How can I modify Save Rec code

  • Thread starter Thread starter Jan Il
  • Start date Start date
J

Jan Il

Hi all - Access 2002 , Windows ME

I have a data entry form used to enter inspections. When I
enter a date in just the 1 year or 2 year control, I get
an error message that says:

Run-Time error 3075
Syntax error in date in query expressiion '[Equip ID]
="19B" And [Monthly]=##

I now have the code below in the Save button.
*******************************
Private Sub cmdSaveRec_Click()
'On Error GoTo Err_cmdSaveRec_Click

If DCount("*", "[Wayside Switch Inspections]", "[Equip ID]
=""" & Me.[Equip ID] & """ And [Monthly]=#" & Me.Monthly
& "#") = 0 Then

DoCmd.DoMenuItem acFormBar, acRecordsMenu,
acSaveRecord, , acMenuVer70

Me!Monthly.DefaultValue = """" & Me!Monthly & """"
DoCmd.GoToRecord , , acNewRec
Me![Equip ID].SetFocus

Else

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

End If

Exit_cmdSaveRec_Click:
Exit Sub

Err_cmdSaveRec_Click:
MsgBox Err.Description
Resume Exit_cmdSaveRec_Click

End Sub
*****************************
When I do the debug, it highlights the line with the
If DCount("*", "[Wayside Switch Inspections]", "[Equip ID]
=""" & Me.[Equip ID] & """ And [Monthly]=#" & Me.Monthly
& "#") = 0 Then

I know what the problem is, but, I just can't figure out
the right syntax to add the correct work around.

What I need to do is add a part to the existing code that
will allow the 1 yr and 2 year inspections, which not not
include the monthly and quarterly inspections. Thus, there
will be entry in those controls to record. I think I just
need to add something to the effect that if there is no
entry in the monthly or quarterly controls, then it can go
ahead and save the 1 year or 2 year inspections. I have
tried to add the modification, but, I can't seem to find
the right code combination for this. Perhaps I need to
just redo the whole code. Or, modify the first part. I am
not sure.

I have checked out the Help files, and tried several of
the examples, but, so far I'm still coming up with errors.

I would appreciate it if someone could advise me as to
the 'type' of work around I need, such an If, Else, Maybe,
Whatever, or some other type of combination. That is the
part I am stumbling on mostly. I have not done this kind
of modification and I can't find the right format that
fits the need.

I would truly appreciate any suggestions.

Best regards,
Jan :)
 
Me.Monthly has no value in it. You have either made a typo for the field
name, or this field that is suppose to have a date in it doesn't.

Ron W
 
Hi Ron!

Ron Weiner said:
Me.Monthly has no value in it. You have either made a typo for the field
name, or this field that is suppose to have a date in it doesn't.

Ron W
Click to expand...

That is right, for the 1 year and 2 year inspections there will be no
Monthly or Quarterly entry made. I know that it is pitching the hissy fit
because there is no date in that field which that part of the code is
looking for. The way it works is, there will always be a monthly if there
is a quarterly, but, there may not always be a quarterly, or a 1 year or a 2
year. In the case of the 1 and 2 year, there will not ever be a monthly or
quarterly.

Dang! Can't do one without the other, and I don't always need the other.
Well..looks like I've got myself boxed into a corner the way it is now. I
don't see that I can get around this so that I don't have to have anything
in the monthly or quarterly to save the 1 year or 2 year. Guess I better go
back and start over.

Thank you very much for your time to assist with this problem, I really do
appreciate it.

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

I have a data entry form used to enter inspections. When I
enter a date in just the 1 year or 2 year control, I get
an error message that says:

Run-Time error 3075
Syntax error in date in query expression '[Equip ID]
="19B" And [Monthly]=##

I now have the code below in the Save button.
*******************************
Private Sub cmdSaveRec_Click()
'On Error GoTo Err_cmdSaveRec_Click

If DCount("*", "[Wayside Switch Inspections]", "[Equip ID]
=""" & Me.[Equip ID] & """ And [Monthly]=#" & Me.Monthly
& "#") = 0 Then

DoCmd.DoMenuItem acFormBar, acRecordsMenu,
acSaveRecord, , acMenuVer70

Me!Monthly.DefaultValue = """" & Me!Monthly & """"
DoCmd.GoToRecord , , acNewRec
Me![Equip ID].SetFocus

Else

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

End If

Exit_cmdSaveRec_Click:
Exit Sub

Err_cmdSaveRec_Click:
MsgBox Err.Description
Resume Exit_cmdSaveRec_Click

End Sub
*****************************
When I do the debug, it highlights the line with the
If DCount("*", "[Wayside Switch Inspections]", "[Equip ID]
=""" & Me.[Equip ID] & """ And [Monthly]=#" & Me.Monthly
& "#") = 0 Then

I know what the problem is, but, I just can't figure out
the right syntax to add the correct work around.

What I need to do is add a part to the existing code that
will allow the 1 yr and 2 year inspections, which does not
include the monthly and quarterly inspections. Thus, there
will be no entry in those controls to record. I think I just
need to add something to the effect that if there is no
entry in the monthly or quarterly controls, then it can go
ahead and save the 1 year or 2 year inspections. I have
tried to add the modification, but, I can't seem to find
the right code combination for this. Perhaps I need to
just redo the whole code. Or, modify the first part. I am
not sure.

I have checked out the Help files, and tried several of
the examples, but, so far I'm still coming up with errors.

I would appreciate it if someone could advise me as to
the 'type' of work around I need, such an If, Else, Maybe,
Whatever, or some other type of combination. That is the
part I am stumbling on mostly. I have not done this kind
of modification and I can't find the right format that
fits the need.

I would truly appreciate any suggestions.

Best regards,
Jan :)
Click to expand...
Click to expand...
 
Back
Top