Solved

Access 2007 VBA Dcount problem 3

Posted on 2010-11-08
7
368 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
Independent Software Vendors: 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

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.
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

740 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