Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 382
  • Last Modified:

Access 2007 VBA Dcount problem 3

I'm embarrassed but still cannot figure out this final piece.

My code:
If DCount("[Part_Number]", "Etching_Schedule", _
            "[Part_Number] = '" & Me.txtPart_Number & "' And [Run_Date] = #" & Me.dteRun_Date & "# And [Shift] = '" & Me.optShift & "'") > 0 Then
            MsgBox "That part number already exists for that date and/or shift."
End If

The Part_Number and Run_Date sections work propberly.  I added the Shift part and it does not generate any errors, but does not find record with the duplicate Part Number, Date, and Shift.

the Shift field data type is TEXT.

Thanks,
Brooks
0
gbnorton
Asked:
gbnorton
3 Solutions
 
hnasrCommented:
Set a break point in code at the DCount statement.
Copy this part and print the value in the Immediate window:

? "[Part_Number] = '" & Me.txtPart_Number & "' And [Run_Date] = #" & Me.dteRun_Date & "# And [Shift] = '" & Me.optShift & "'"

Copy the output and check data types.
List the output here.
0
 
rockiroadsCommented:
in your last post of this, I suggested splitting into a variable so it is easier to debug

dim sWhere as string

sWhere = "[Part_Number] = '" & Me.txtPart_Number & "' And [Run_Date] = #" & Me.dteRun_Date & "# And [Shift] = '" & Me.optShift & "'"

debug.print "SEARCH CRITERIA IS"
debug.print sWhere

If DCount("[Part_Number]", "Etching_Schedule", sWhere) > 0 Then
    MsgBox "That part number already exists for that date and/or shift."
End If



Now if u run this, then look at the vba editor, in particular the immediate window. You will see outputted there

SEARCH CRITER IS
PartNumber ...


Take that generated sql (PartnUmber = ...)

create a new query, go to sql view then add this

select * from Etching)_Schedule where <the line generated goes here>

then run the query

if that doesnt produce anything then goes to show nothing wrong with the code as such since the data does not exist

so double check the values

perhaps you got a space in optShift? you can try placing trim around Me.optShift for example
0
 
GRayLCommented:
If you are using a set of option buttons inside a frame, then you may not be referencing the value of the option group correctly.  optShift looks to be a 'strange' name for the frame holding the group of option buttons.  When running, the frame picks of the value assigned to the active option button.
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
GRayLCommented:
'...the frame picks the value...'
0
 
Rey Obrero (Capricorn1)Commented:
try this



If DCount("[Part_Number]", "Etching_Schedule", _
            "[Part_Number] = '" & Me.txtPart_Number & "' And [Run_Date] = #" & Me.dteRun_Date & "# And [Shift] = '" & cstr(Me.optShift) & "'") > 0 Then
0
 
gbnortonAuthor Commented:
Hnasr, thanks for showing me how to insert that break point.

Rockiroads, I will use your suggestion in the future when using DCount.

GRayL,  That was it.  The option buttons are saved as 1, 2.  I was converting them to AM, PM...  AFTER the  record check.  

Thanks,
Brooks
0
 
GRayLCommented:
Thanks, glad to help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now