Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Access 2007 VBA Dcount problem 3

Posted on 2010-11-08
7
Medium Priority
?
373 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 31

Assisted Solution

by:hnasr
hnasr earned 200 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 400 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 1400 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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
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

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
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…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

705 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