Solved

Access 2007 VBA Dcount problem 3

Posted on 2010-11-08
7
363 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
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 44

Expert Comment

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

Expert Comment

by:Rey Obrero
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

706 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now