Duplicates, but not within same date period

[Guest]

Good day,

I have a main form "frmCostAndRevenue" which includes the CARID (PK),
CARDate and DeptNbr. I have a subform "fsubCostAndRevenue" (linked via
CARID), which includes all the details (one field being cboEquipmentId) for
the particular reporting period (CARDate).

What I need to be able to do is, allow duplicate Equipment Id Nbrs
(cboEquipmentId) in the table (tblCostAndRevenueDetail), but not within the
same reporting period. ie: User enters R1150 for March 31, 2006 reporting
period and then tries to enter that same unit again for the same reporting
period I want a MsgBox telling them, This Unit alreadys exists for this
reporting period. Choose another number).

I've tried to adapt code I've found in this forum but have not been
succesful as I cannot figure out how to include the date criteria.

If anyone can shed some light on this I would truly greatful.

Thanks and have a great day.

 

[Jeff L]

You will need to perform some kind of check on your two fields:

Dim qry as string, rst as object

qry = "Select * From YourTableName " & _
"Where cboEquipmentId = '" & Me.EquipmentIDField & "' " & _
"And CARDate = '" & Me.CARDate & "';"

Set rst = CurrentDB.OpenRecordSet(qry)

If rst.eof = False then
'Reporting period exists
Msgbox "This Unit alreadys exists for this reporting period. Choose
another number.", vbOkOnly
More Code if needed
End if

Hope that helps!

 

[Guest]

Hi Jeff,

Thanks for replying so quickly. I tried your code and when I went to test
it I got the following error message:

Run-Time Error '3131'
Syntax Error in From Clause
and Set rst = CurrentDB.OpenRecordSet(qry) was hi-lited.

I'm afraid I'm totally lost at this point. The only change I made to your
code was instead of my TableName I used my Query that the subform is based
on. Would this cause any problems?

Here is a copy of what I put:

Private Sub cboEquipmentId_AfterUpdate()

Dim qry As String
Dim rst As Object

qry = "Select * From qselCostAndRevenueDetail" & "Where cboEquipmentId = '"
& Me.cboEquipmentId & "'" & "CARDate = '" & Me.CARDate & "';"

Set rst = CurrentDb.OpenRecordset(qry)

If rst.EOF = False Then
'Reporting period exists
MsgBox "This Unit already exists for this reporting period. Choose another
number.", vbOKOnly

End If

End Sub

Any suggestions?

Thanks Kindly

 

[Jeff L]

The syntax error comes from the qry. You are missing some spaces in
the statement and also an AND. Try this:
qry = "Select * From qselCostAndRevenueDetail" & " Where cboEquipmentId
= '"
& Me.cboEquipmentId & "' And CARDate = '" & Me.CARDate & "';"

Hope that helps!

 

[Guest]

Hi Jeff,

First off let me say Thank you for sticking with me on this and I'm sorry to
be such a pain, but...

I changed the code accordingly (actually I cut and pasted yours in) and now
I get a new error.

Run-Time Error 3061
Too few parameters. Expected 1.

And set rst = CurrentDB.OpenRecordSet(qry) is again hi-lited in yellow.

Still lost/confused. Any other suggestions.

Thanks Kindly

 

[Jeff L]

No problem.

Time to debug. Put this after the qry statement:
MsgBox qry, vbOKOnly

You should get a message box pop up with qry's value and the values you
entered should be in there. So it would be something like:

Select * From qselCostAndRevenueDetail Where cboEquipmentID = 'R1150'
And CARDate = 'March 31, 2006';

Check it for a missing space or something out of the ordinary and we
can go from there.

 

[Guest]

Hi Jeff,

I tried what you suggested and the only thing that I can see that's
different is the date. You show And CARDate = 'March 31, 2006'; and the
MsgBox shows And CARDate = '03/31/2006';

Would this do it ?

Thanks

 

[Jeff L]

No it shouldn't be a problem. I put it in that way because that's how
you had it in your original post. If you are still getting that error
with parameters expected, make sure that you have everything named
correctly in the qry statement...ie everything is spelled correctly and
that sort of thing. You could also try putting a # around the date
value instead of the single quotes.

Let me know how it goes.

 

[Guest]

Hello agan Jeff,

Just wanted to say a BIG Thank you for helping me yesterday. I was actually
able to get this to work this morning. First off, I could of made this lot
easier on myself (and not wasted your time) had I only referenced the correct
control name (duh).

It seems to work with this code:

qry = "Select * From qselCostAndRevenueDetail" & " Where EquipmentId = '" &
Me.EquipmentId & "' And CARDate = #" & Me.CARDate & "#;"

So again, I thank you for all of your help. I really appreciate it.

Have a good day.
Gabby Girl