Solved

Access 2007 VBA Dcount problem 3

Posted on 2010-11-08
7
369 Views
Last Modified: 2012-05-10
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
Comment
Question by:gbnorton
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 30

Assisted Solution

by:hnasr
hnasr earned 50 total points
ID: 34088043
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
 
LVL 65

Assisted Solution

by:rockiroads
rockiroads earned 100 total points
ID: 34088060
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
 
LVL 44

Accepted Solution

by:
GRayL earned 350 total points
ID: 34088063
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 44

Expert Comment

by:GRayL
ID: 34088077
'...the frame picks the value...'
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34088320
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
 

Author Closing Comment

by:gbnorton
ID: 34094397
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
 
LVL 44

Expert Comment

by:GRayL
ID: 34095192
Thanks, glad to help.
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question