Search

[croth68]

I used the code reference from allenbrowne.com to build a continous form with
pretty good search capability. The problem I am having on this is if I enter
a dealer contact last name it returns a popup box that asks for the value,
you have to retype the dealer contact last name before it will return results.
How do I get it to not bring up the pop up box and use the data that was
entered the first time on the form. Please help, below is the code I have for
the search section. I have other code for the form and can post all the code
for the entire form if needed, other code includes pop up calendar code and
print data to form code. Thanks.

Private Sub cmdFilter_Click()
'Purpose: Build up the criteria string form the non-blank search boxes,
and apply to the form's Filter.
'Notes: 1. We tack " AND " on the end of each condition so you can
easily add more search boxes; _
we remove the trailing " AND " at the end.
' 2. The date range works like this: _
Both dates = only dates between (both inclusive.
_
Start date only = all dates from this one onwards; _
End date only = all dates up to (and including this
one).
Dim strWhere As String 'The criteria string.
Dim lngLen As Long 'Length of the criteria string to
append to.
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for dates in
a JET query string.

'***********************************************************************
'Look at each search box, and build up the criteria string from the non-
blank ones.
'***********************************************************************
'Text field example. Use quotes around the value in the string.
If Not IsNull(Me.Combo44) Then
strWhere = strWhere & "([Training Aid ID] = " & Me.Combo44 & ") AND "
End If

'Another text field example. Use Like to find anywhere in the field.
If Not IsNull(Me.Text46) Then
strWhere = strWhere & "([Dealer ID] Like ""*" & Me.Text46 & "*"") AND
"
End If

'Number field example. Do not add the extra quotes.
If Not IsNull(Me.Text52) Then
strWhere = strWhere & "([Class Name] = " & Me.Text52 & ") AND "
End If

If Not IsNull(Me.Text62) Then
strWhere = strWhere & " ([Dealer Name] = " & Me.Text62 & ") AND "
End If

If Not IsNull(Me.Text75) Then
strWhere = strWhere & " ([Dealer City] = " & Me.Text75 & ") AND "
End If

If Not IsNull(Me.Text68) Then
strWhere = strWhere & " ([Dealer Zip] = " & Me.Text68 & ") AND "
End If

If Not IsNull(Me.Text64) Then
strWhere = strWhere & " ([Dealer Contact Last Name] = " & Me.Text64 &
") AND "
End If

If Not IsNull(Me.Text66) Then
strWhere = strWhere & " ([Dealer Contact First Name] = " & Me.Text66
& ") AND "
End If

If Not IsNull(Me.Text79) Then
strWhere = strWhere & " ([Check Out] = """ & Me.Text79 & """) AND "
End If

If Not IsNull(Me.Text81) Then
strWhere = strWhere & " ([Check In] = " & Me.Text81 & ") AND "
End If

'Yes/No field and combo example. If combo is blank or contains "ALL", we
do nothing.
'If Me.cboFilterIsCorporate = -1 Then
'strWhere = strWhere & "([IsCorporate] = True) AND "
'ElseIf Me.cboFilterIsCorporate = 0 Then
'strWhere = strWhere & "([IsCorporate] = False) AND "
'End If

'Date field example. Use the format string to add the # delimiters and
get the right international format.
If Not IsNull(Me.Text48) Then
strWhere = strWhere & "([Date Out] >= " & Format(Me.Text48,
conJetDate) & ") AND "
End If

'Another date field example. Use "less than the next day" since this
field has times as well as dates.
If Not IsNull(Me.Text50) Then 'Less than the next day.
strWhere = strWhere & "([Date Out] < " & Format(Me.Text50 + 1,
conJetDate) & ") AND "
End If

If Not IsNull(Me.Text73) Then
strWhere = strWhere & "([Date Needed] >= " & Format(Me.Text73,
conJetDate) & ") AND "
End If

If Not IsNull(Me.Text77) Then
strWhere = strWhere & " ([Date Needed] >= " & Format(Me.Text77,
conJetDate) & ") AND "
End If

If Not IsNull(Me.Text97) Then
strWhere = strWhere & "([Date In] >= " & Format(Me.Text97, conJetDate)
& ") AND "
End If

If Not IsNull(Me.Text99) Then 'Less than the next day.
strWhere = strWhere & "([Date In] < " & Format(Me.Text99 + 1,
conJetDate) & ") AND "
End If

If Not IsNull(Me.Text101) Then
strWhere = strWhere & "([Return Date] >= " & Format(Me.Text101,
conJetDate) & ") AND "
End If

'Another date field example. Use "less than the next day" since this
field has times as well as dates.
If Not IsNull(Me.Text103) Then 'Less than the next day.
strWhere = strWhere & "([Return Date] < " & Format(Me.Text103 + 1,
conJetDate) & ") AND "
End If

If Not IsNull(Me.Text93) Then
strWhere = strWhere & "([Class Start Date] >= " & Format(Me.Text93,
conJetDate) & ") AND "
End If

'Another date field example. Use "less than the next day" since this
field has times as well as dates.
If Not IsNull(Me.Text107) Then 'Less than the next day.
strWhere = strWhere & "([Class Start Date] < " & Format(Me.Text107 +
1, conJetDate) & ") AND "
End If

If Not IsNull(Me.Text95) Then
strWhere = strWhere & "([Class End Date] >= " & Format(Me.Text95,
conJetDate) & ") AND "
End If

'Another date field example. Use "less than the next day" since this
field has times as well as dates.
If Not IsNull(Me.Text105) Then 'Less than the next day.
strWhere = strWhere & "([Class End Date] < " & Format(Me.Text105 + 1,
conJetDate) & ") AND "
End If
'***********************************************************************
'Chop off the trailing " AND ", and use the string as the form's Filter.
'***********************************************************************
'See if the string has more than 5 characters (a trailng " AND ") to
remove.
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove the "
AND " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line. Prints to
Immediate Window (Ctrl+G).
Debug.Print strWhere

'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
End If

End Sub

Private Sub cmdReset_Click()
'Purpose: Clear all the search boxes in the Form Header, and show all
records again.
Dim ctl As Control

'Clear all the controls in the Form Header section.
For Each ctl In Me.Section(acHeader).Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox
ctl.Value = Null
'Case acCheckBox
' ctl.Value = False
End Select
Next

'Remove the form's filter.
Me.FilterOn = False
End Sub

Private Sub Form_BeforeInsert(Cancel As Integer)
'To avoid problems if the filter returns no records, we did not set its
AllowAdditions to No.
'We prevent new records by cancelling the form's BeforeInsert event
instead.
'The problems are explained at http://allenbrowne.com/bug-06.html
Cancel = True
MsgBox "You cannot add new clients to the search form.", vbInformation,
"Permission denied."
End Sub

Private Sub Form_Open(Cancel As Integer)
'Remove the single quote from these lines if you want to initially show
no records.
'Me.Filter = "(False)"
'Me.FilterOn = True
End Sub

Private Sub txtFormFilter_BeforeUpdate(Cancel As Integer)

End Sub

Private Sub Command58_Click()
On Error GoTo Err_Command58_Click

DoCmd.RunMacro

Exit_Command58_Click:
Exit Sub

Err_Command58_Click:
MsgBox Err.Description
Resume Exit_Command58_Click

End Sub

 

[Evi]

Is it possible that your report is based on a Parameter query, or that you
have a control in it which looks for a parameter?
Do you have a control called Dealer Contact Last Name in your report?
Evi

I used the code reference from allenbrowne.com to build a continous form with
pretty good search capability. The problem I am having on this is if I enter
a dealer contact last name it returns a popup box that asks for the value,
you have to retype the dealer contact last name before it will return results.
How do I get it to not bring up the pop up box and use the data that was
entered the first time on the form. Please help, below is the code I have for
the search section. I have other code for the form and can post all the code
for the entire form if needed, other code includes pop up calendar code and
print data to form code. Thanks.

Private Sub cmdFilter_Click()
'Purpose: Build up the criteria string form the non-blank search boxes,
and apply to the form's Filter.
'Notes: 1. We tack " AND " on the end of each condition so you can
easily add more search boxes; _
we remove the trailing " AND " at the end.
' 2. The date range works like this: _
Both dates = only dates between (both inclusive.
_
Start date only = all dates from this one onwards; _
End date only = all dates up to (and including this
one).
Dim strWhere As String 'The criteria string.
Dim lngLen As Long 'Length of the criteria string to
append to.
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for dates in
a JET query string.

'***********************************************************************
'Look at each search box, and build up the criteria string from the non-
blank ones.
'***********************************************************************
'Text field example. Use quotes around the value in the string.
If Not IsNull(Me.Combo44) Then
strWhere = strWhere & "([Training Aid ID] = " & Me.Combo44 & ") AND "
End If

'Another text field example. Use Like to find anywhere in the field.
If Not IsNull(Me.Text46) Then
strWhere = strWhere & "([Dealer ID] Like ""*" & Me.Text46 & "*"") AND
"
End If

'Number field example. Do not add the extra quotes.
If Not IsNull(Me.Text52) Then
strWhere = strWhere & "([Class Name] = " & Me.Text52 & ") AND "
End If

If Not IsNull(Me.Text62) Then
strWhere = strWhere & " ([Dealer Name] = " & Me.Text62 & ") AND "
End If

If Not IsNull(Me.Text75) Then
strWhere = strWhere & " ([Dealer City] = " & Me.Text75 & ") AND "
End If

If Not IsNull(Me.Text68) Then
strWhere = strWhere & " ([Dealer Zip] = " & Me.Text68 & ") AND "
End If

If Not IsNull(Me.Text64) Then
strWhere = strWhere & " ([Dealer Contact Last Name] = " & Me.Text64 &
") AND "
End If

If Not IsNull(Me.Text66) Then
strWhere = strWhere & " ([Dealer Contact First Name] = " & Me.Text66
& ") AND "
End If

If Not IsNull(Me.Text79) Then
strWhere = strWhere & " ([Check Out] = """ & Me.Text79 & """) AND "
End If

If Not IsNull(Me.Text81) Then
strWhere = strWhere & " ([Check In] = " & Me.Text81 & ") AND "
End If

'Yes/No field and combo example. If combo is blank or contains "ALL", we
do nothing.
'If Me.cboFilterIsCorporate = -1 Then
'strWhere = strWhere & "([IsCorporate] = True) AND "
'ElseIf Me.cboFilterIsCorporate = 0 Then
'strWhere = strWhere & "([IsCorporate] = False) AND "
'End If

'Date field example. Use the format string to add the # delimiters and
get the right international format.
If Not IsNull(Me.Text48) Then
strWhere = strWhere & "([Date Out] >= " & Format(Me.Text48,
conJetDate) & ") AND "
End If

'Another date field example. Use "less than the next day" since this
field has times as well as dates.
If Not IsNull(Me.Text50) Then 'Less than the next day.
strWhere = strWhere & "([Date Out] < " & Format(Me.Text50 + 1,
conJetDate) & ") AND "
End If

If Not IsNull(Me.Text73) Then
strWhere = strWhere & "([Date Needed] >= " & Format(Me.Text73,
conJetDate) & ") AND "
End If

If Not IsNull(Me.Text77) Then
strWhere = strWhere & " ([Date Needed] >= " & Format(Me.Text77,
conJetDate) & ") AND "
End If

If Not IsNull(Me.Text97) Then
strWhere = strWhere & "([Date In] >= " & Format(Me.Text97, conJetDate)
& ") AND "
End If

If Not IsNull(Me.Text99) Then 'Less than the next day.
strWhere = strWhere & "([Date In] < " & Format(Me.Text99 + 1,
conJetDate) & ") AND "
End If

If Not IsNull(Me.Text101) Then
strWhere = strWhere & "([Return Date] >= " & Format(Me.Text101,
conJetDate) & ") AND "
End If

'Another date field example. Use "less than the next day" since this
field has times as well as dates.
If Not IsNull(Me.Text103) Then 'Less than the next day.
strWhere = strWhere & "([Return Date] < " & Format(Me.Text103 + 1,
conJetDate) & ") AND "
End If

If Not IsNull(Me.Text93) Then
strWhere = strWhere & "([Class Start Date] >= " & Format(Me.Text93,
conJetDate) & ") AND "
End If

'Another date field example. Use "less than the next day" since this
field has times as well as dates.
If Not IsNull(Me.Text107) Then 'Less than the next day.
strWhere = strWhere & "([Class Start Date] < " & Format(Me.Text107 +
1, conJetDate) & ") AND "
End If

If Not IsNull(Me.Text95) Then
strWhere = strWhere & "([Class End Date] >= " & Format(Me.Text95,
conJetDate) & ") AND "
End If

'Another date field example. Use "less than the next day" since this
field has times as well as dates.
If Not IsNull(Me.Text105) Then 'Less than the next day.
strWhere = strWhere & "([Class End Date] < " & Format(Me.Text105 + 1,
conJetDate) & ") AND "
End If
'***********************************************************************
'Chop off the trailing " AND ", and use the string as the form's Filter.'***********************************************************************
'See if the string has more than 5 characters (a trailng " AND ") to
remove.
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove the "
AND " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line. Prints to
Immediate Window (Ctrl+G).
Debug.Print strWhere

'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
End If

End Sub

Private Sub cmdReset_Click()
'Purpose: Clear all the search boxes in the Form Header, and show all
records again.
Dim ctl As Control

'Clear all the controls in the Form Header section.
For Each ctl In Me.Section(acHeader).Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox
ctl.Value = Null
'Case acCheckBox
' ctl.Value = False
End Select
Next

'Remove the form's filter.
Me.FilterOn = False
End Sub

Private Sub Form_BeforeInsert(Cancel As Integer)
'To avoid problems if the filter returns no records, we did not set its
AllowAdditions to No.
'We prevent new records by cancelling the form's BeforeInsert event
instead.
'The problems are explained at http://allenbrowne.com/bug-06.html
Cancel = True
MsgBox "You cannot add new clients to the search form.", vbInformation,
"Permission denied."
End Sub

Private Sub Form_Open(Cancel As Integer)
'Remove the single quote from these lines if you want to initially show
no records.
'Me.Filter = "(False)"
'Me.FilterOn = True
End Sub

Private Sub txtFormFilter_BeforeUpdate(Cancel As Integer)

End Sub

Private Sub Command58_Click()
On Error GoTo Err_Command58_Click

DoCmd.RunMacro

Exit_Command58_Click:
Exit Sub

Err_Command58_Click:
MsgBox Err.Description
Resume Exit_Command58_Click

End Sub

 

[Dale Fye]

My first thought is that you need to come up with a naming convention for
your controls. Combo44 means absolutely nothing to anyone tring to debug
your code, but cbo_TrainingAidID would.

My second thought is that you don't actually have a field called [Dealer
Contact Last Name] in your table. Check to make sure that it is spelled
exactly like the field name in the data table. Sometimes spaces can be
misleading (2 where there should only be one), so I never put a space in a
field name. Instead, if I want my field names to be more readable, I'll
insert an underscore. The other advantage of this is that you don't have to
wrap Dealer_Contact_Last_Name in brackets, but you do [Dealer Contact Last
Name].

--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

I used the code reference from allenbrowne.com to build a continous form with
pretty good search capability. The problem I am having on this is if I enter
a dealer contact last name it returns a popup box that asks for the value,
you have to retype the dealer contact last name before it will return results.
How do I get it to not bring up the pop up box and use the data that was
entered the first time on the form. Please help, below is the code I have for
the search section. I have other code for the form and can post all the code
for the entire form if needed, other code includes pop up calendar code and
print data to form code. Thanks.

Private Sub cmdFilter_Click()
'Purpose: Build up the criteria string form the non-blank search boxes,
and apply to the form's Filter.
'Notes: 1. We tack " AND " on the end of each condition so you can
easily add more search boxes; _
we remove the trailing " AND " at the end.
' 2. The date range works like this: _
Both dates = only dates between (both inclusive.
_
Start date only = all dates from this one onwards; _
End date only = all dates up to (and including this
one).
Dim strWhere As String 'The criteria string.
Dim lngLen As Long 'Length of the criteria string to
append to.
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for dates in
a JET query string.

'***********************************************************************
'Look at each search box, and build up the criteria string from the non-
blank ones.
'***********************************************************************
'Text field example. Use quotes around the value in the string.
If Not IsNull(Me.Combo44) Then
strWhere = strWhere & "([Training Aid ID] = " & Me.Combo44 & ") AND "
End If

'Another text field example. Use Like to find anywhere in the field.
If Not IsNull(Me.Text46) Then
strWhere = strWhere & "([Dealer ID] Like ""*" & Me.Text46 & "*"") AND
"
End If

'Number field example. Do not add the extra quotes.
If Not IsNull(Me.Text52) Then
strWhere = strWhere & "([Class Name] = " & Me.Text52 & ") AND "
End If

If Not IsNull(Me.Text62) Then
strWhere = strWhere & " ([Dealer Name] = " & Me.Text62 & ") AND "
End If

If Not IsNull(Me.Text75) Then
strWhere = strWhere & " ([Dealer City] = " & Me.Text75 & ") AND "
End If

If Not IsNull(Me.Text68) Then
strWhere = strWhere & " ([Dealer Zip] = " & Me.Text68 & ") AND "
End If

If Not IsNull(Me.Text64) Then
strWhere = strWhere & " ([Dealer Contact Last Name] = " & Me.Text64 &
") AND "
End If

If Not IsNull(Me.Text66) Then
strWhere = strWhere & " ([Dealer Contact First Name] = " & Me.Text66
& ") AND "
End If

If Not IsNull(Me.Text79) Then
strWhere = strWhere & " ([Check Out] = """ & Me.Text79 & """) AND "
End If

If Not IsNull(Me.Text81) Then
strWhere = strWhere & " ([Check In] = " & Me.Text81 & ") AND "
End If

'Yes/No field and combo example. If combo is blank or contains "ALL", we
do nothing.
'If Me.cboFilterIsCorporate = -1 Then
'strWhere = strWhere & "([IsCorporate] = True) AND "
'ElseIf Me.cboFilterIsCorporate = 0 Then
'strWhere = strWhere & "([IsCorporate] = False) AND "
'End If

'Date field example. Use the format string to add the # delimiters and
get the right international format.
If Not IsNull(Me.Text48) Then
strWhere = strWhere & "([Date Out] >= " & Format(Me.Text48,
conJetDate) & ") AND "
End If

'Another date field example. Use "less than the next day" since this
field has times as well as dates.
If Not IsNull(Me.Text50) Then 'Less than the next day.
strWhere = strWhere & "([Date Out] < " & Format(Me.Text50 + 1,
conJetDate) & ") AND "
End If

If Not IsNull(Me.Text73) Then
strWhere = strWhere & "([Date Needed] >= " & Format(Me.Text73,
conJetDate) & ") AND "
End If

If Not IsNull(Me.Text77) Then
strWhere = strWhere & " ([Date Needed] >= " & Format(Me.Text77,
conJetDate) & ") AND "
End If

If Not IsNull(Me.Text97) Then
strWhere = strWhere & "([Date In] >= " & Format(Me.Text97, conJetDate)
& ") AND "
End If

If Not IsNull(Me.Text99) Then 'Less than the next day.
strWhere = strWhere & "([Date In] < " & Format(Me.Text99 + 1,
conJetDate) & ") AND "
End If

If Not IsNull(Me.Text101) Then
strWhere = strWhere & "([Return Date] >= " & Format(Me.Text101,
conJetDate) & ") AND "
End If

'Another date field example. Use "less than the next day" since this
field has times as well as dates.
If Not IsNull(Me.Text103) Then 'Less than the next day.
strWhere = strWhere & "([Return Date] < " & Format(Me.Text103 + 1,
conJetDate) & ") AND "
End If

If Not IsNull(Me.Text93) Then
strWhere = strWhere & "([Class Start Date] >= " & Format(Me.Text93,
conJetDate) & ") AND "
End If

'Another date field example. Use "less than the next day" since this
field has times as well as dates.
If Not IsNull(Me.Text107) Then 'Less than the next day.
strWhere = strWhere & "([Class Start Date] < " & Format(Me.Text107 +
1, conJetDate) & ") AND "
End If

If Not IsNull(Me.Text95) Then
strWhere = strWhere & "([Class End Date] >= " & Format(Me.Text95,
conJetDate) & ") AND "
End If

'Another date field example. Use "less than the next day" since this
field has times as well as dates.
If Not IsNull(Me.Text105) Then 'Less than the next day.
strWhere = strWhere & "([Class End Date] < " & Format(Me.Text105 + 1,
conJetDate) & ") AND "
End If
'***********************************************************************
'Chop off the trailing " AND ", and use the string as the form's Filter.
'***********************************************************************
'See if the string has more than 5 characters (a trailng " AND ") to
remove.
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove the "
AND " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line. Prints to
Immediate Window (Ctrl+G).
Debug.Print strWhere

'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
End If

End Sub

Private Sub cmdReset_Click()
'Purpose: Clear all the search boxes in the Form Header, and show all
records again.
Dim ctl As Control

'Clear all the controls in the Form Header section.
For Each ctl In Me.Section(acHeader).Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox
ctl.Value = Null
'Case acCheckBox
' ctl.Value = False
End Select
Next

'Remove the form's filter.
Me.FilterOn = False
End Sub

Private Sub Form_BeforeInsert(Cancel As Integer)
'To avoid problems if the filter returns no records, we did not set its
AllowAdditions to No.
'We prevent new records by cancelling the form's BeforeInsert event
instead.
'The problems are explained at http://allenbrowne.com/bug-06.html
Cancel = True
MsgBox "You cannot add new clients to the search form.", vbInformation,
"Permission denied."
End Sub

Private Sub Form_Open(Cancel As Integer)
'Remove the single quote from these lines if you want to initially show
no records.
'Me.Filter = "(False)"
'Me.FilterOn = True
End Sub

Private Sub txtFormFilter_BeforeUpdate(Cancel As Integer)

End Sub

Private Sub Command58_Click()
On Error GoTo Err_Command58_Click

DoCmd.RunMacro

Exit_Command58_Click:
Exit Sub

Err_Command58_Click:
MsgBox Err.Description
Resume Exit_Command58_Click

End Sub

 

[croth68 via AccessMonster.com]

Thanks Folks, I will go back through my code and rename all the Text and
Combo, Thinking about it this problem did not start until I Programmed the
pop up calendar part to make it easier to enter search dates. I will repost
as soon as I get the code cleaned up. I am very new to programming so your
suggestions help tremendously.

 

[croth68 via AccessMonster.com]

I think I figured it out because it seems to be working. Cleaning up the code
helped a lot. I am not sure why this worked but it did, here is what fixed it
(note this is just a sample of the code):

I had this, with one quote

If Not IsNull(Me.Dealer_Zip_Search) Then
strWhere = strWhere & " ([Dealer Zip] = " & Me.Dealer_Zip_Search & ")
AND "
End If

I changed it to this, with three quotes

If Not IsNull(Me.Dealer_Zip_Search) Then
strWhere = strWhere & " ([Dealer Zip] = """ & Me.Dealer_Zip_Search &
""") AND "
End If

I found the same result on one of my date values and in that case found that
the field name was not correct, I corrected the field name and problem solved.

I am not sure why this fixed it. Maybe someone can explain it to me. I did
have another question though, I have a form that allows me to enter all the
information into the main table (which is the table this search is based on).
There are a couple different dates that I would like to be able to compare.
Here is an example:
If training aid number 1 is scheduled to go out to a dealer or for a class,
when someone tries to input information for training aid one to go to a
different dealer or class then a message box pops up displaying an
unavailable message. I think this can be done by looking at the date fields
because there is a dealer need date field, dealer return date field, class
start date field, class end date field, training aid in date field and
training aid out date field. I am thinking something like this if training
aid 1 is scheduled for class x and class x start date is y then when a new
record with training aid 1 is scheduled then look at training aid 1 to see if
date in field is null or before the date training aid 1 is needed. Let me
know if this makes no sense at all. Would a Dlookup function work for this?

 

[Dale Fye]

The reason the first change worked is that your [Dealer Zip] field is a text
data type, not a number. Whenever you build SQL strings on the fly like
this, you must delimit literal text values with either a quote (as you did
it, this actually involves adding 2 quotes), or with an apostrophe:

If Not IsNull(Me.Dealer_Zip_Search) Then
strWhere = strWhere & " ([Dealer Zip] = '" & Me.Dealer_Zip_Search &
"') AND "
End If

Your second question is a little more complicated. As I see it, you have
dealer need date, dealer return date, class start date, and class end date
and it sounds like all of these are separate fields that are available in
the same record, is that correct? And if they are all in the same record,
it sounds like the Class and Dealer dates are mutually exclusive (you will
have either Class dates or Dealer dates, but not both), is that correct.
If so you will need to do something like count the number of records already
in the database for Training Aid #1 where the Class Start Date is < [Dealer
Return Date] OR [Class End Date] (whichever is filled in) AND the Class End
Date > the [Dealer Need Date] or [Class Start Date] (again, whichever is
filled in). This may seem counter intuitive, but basically what you have is
the potential for 4 situations that could cause an availability conflict.

1. [Class Start Date] < [Dealer Need Date] AND [Class End Date] < [Dealer
Return Date]
2. [Class Start Date] < [Dealer Need Date] AND [Class End Date] > [Dealer
Return Date]
3. [Class Start Date] > [Dealer Need Date] AND [Class End Date] < [Dealer
Return Date]
4. [Class Start Date] > [Dealer Need Date] AND [Class End Date] > [Dealer
Return Date]

The comparison I used above will identify those records where any of these
potential conflicts exists. So, basically, I would create a criteria
string, then use the DCOUNT function to count the number of records (other
than the current one) which meet that criteria. If it is more than zero,
then you have a potential conflict. The down side of this is that you
cannot really do this comparison until you know both the ClassStartDate and
ClassEndDate or DealerNeedDate and DealerReturnDate, so you would need to
put this in the forms Before Update event; something like:

Private sub Form_BeforeUpdate(Cancel as integer)

Dim strCriteria as string

'Put some tests in here to make sure that both ClassStart and End or
DealerNeed and Return dates are entered

If NOT ISNULL(me.txt_Class_Start_Date) then
strCriteria = "[TrainingItemID] = " & me.cbo_TrainingItemID _
& " AND #" & me.txt_Class_Start_Date & "# <
NZ([Dealer Return Date], [Class End Date]" _
& " AND #" & me.txt_Class_End_Date & "# > NZ([Dealer
Need Date], [Class Start Date]"
Else
strCriteria = "[TrainingItemID] = " & me.cbo_TrainingItemID _
& " AND #" & me.txt_Dealer_Need_Date & "# <
NZ([Dealer Return Date], [Class End Date]" _
& " AND #" & me.txt_Dealer_Return_Date & "# >
NZ([Dealer Need Date], [Class Start Date]"
End if
Debug.print strCriteria
if Dcount("ID", "yourTableName", strCriteria) > 0 then
msgbox "This period conflicts with a previously scheduled event.", _
vbcritical + vbOKOnly, _
"The item you requested is not available
Cancel = true
end if

End Sub

Instead of testing against zero (0), the If statement that includes the
DCOUNT could also test against the number of the particular training item
you are looking for, something like:

IF Dcount("ID", "yourTableName", strCriteria) >= DLOOKUP("InStock",
"tblTrainingItems", "TrainingItemID = " & me.cbo_TrainingItemID) Then

HTH
Dale

I think I figured it out because it seems to be working. Cleaning up the
code
helped a lot. I am not sure why this worked but it did, here is what fixed
it
(note this is just a sample of the code):

I had this, with one quote

If Not IsNull(Me.Dealer_Zip_Search) Then
strWhere = strWhere & " ([Dealer Zip] = " & Me.Dealer_Zip_Search &
")
AND "
End If

I changed it to this, with three quotes

If Not IsNull(Me.Dealer_Zip_Search) Then
strWhere = strWhere & " ([Dealer Zip] = """ & Me.Dealer_Zip_Search
&
""") AND "
End If

I found the same result on one of my date values and in that case found
that
the field name was not correct, I corrected the field name and problem
solved.

I am not sure why this fixed it. Maybe someone can explain it to me. I did
have another question though, I have a form that allows me to enter all
the
information into the main table (which is the table this search is based
on).
There are a couple different dates that I would like to be able to
compare.
Here is an example:
If training aid number 1 is scheduled to go out to a dealer or for a
class,
when someone tries to input information for training aid one to go to a
different dealer or class then a message box pops up displaying an
unavailable message. I think this can be done by looking at the date
fields
because there is a dealer need date field, dealer return date field, class
start date field, class end date field, training aid in date field and
training aid out date field. I am thinking something like this if training
aid 1 is scheduled for class x and class x start date is y then when a new
record with training aid 1 is scheduled then look at training aid 1 to see
if
date in field is null or before the date training aid 1 is needed. Let me
know if this makes no sense at all. Would a Dlookup function work for
this?

Thanks Folks, I will go back through my code and rename all the Text and
Combo, Thinking about it this problem did not start until I Programmed the
pop up calendar part to make it easier to enter search dates. I will
repost
as soon as I get the code cleaned up. I am very new to programming so your
suggestions help tremendously.

 

[croth68 via AccessMonster.com]

Dale,

Thanks for getting back to me. I will warn you up front I am programming
stupid, I have been able to do what I have done so far really from this forum
and from knowledgeable people like yourself. The last programming I was
exposed to was very simple C++, which was 7 years ago or so, some of the
rules are coming back but sometimes they are the wrong rules, lol.

You are correct that the information will be added to the same record as
separate fields, there will be either a class start date etc or dealer need
date etc (you are also correct on the either/or) there will be an in date
field and an out date field that will be filled in regardless of weather it
is a dealer needing the training aid or a class needing the training aid. I
should be able to add an If Not IsNull(Date In) Then statement to the code.
If I am thinking right this should count all the records for training aid x
and if there is no date filled in for Date in then should list that training
aid as unavailable which would be a msgbox command if I am not mistaken.
Thanks for the help on this I appreciate it, my email is c_r_o_t_h_6_8@c_o_x.
net. I tried to code the my email to keep spammers away, take away all of the
underscores and there you go. Thanks again for the help.

The reason the first change worked is that your [Dealer Zip] field is a text
data type, not a number. Whenever you build SQL strings on the fly like
this, you must delimit literal text values with either a quote (as you did
it, this actually involves adding 2 quotes), or with an apostrophe:

If Not IsNull(Me.Dealer_Zip_Search) Then
strWhere = strWhere & " ([Dealer Zip] = '" & Me.Dealer_Zip_Search &
"') AND "
End If

Your second question is a little more complicated. As I see it, you have
dealer need date, dealer return date, class start date, and class end date
and it sounds like all of these are separate fields that are available in
the same record, is that correct? And if they are all in the same record,
it sounds like the Class and Dealer dates are mutually exclusive (you will
have either Class dates or Dealer dates, but not both), is that correct.
If so you will need to do something like count the number of records already
in the database for Training Aid #1 where the Class Start Date is < [Dealer
Return Date] OR [Class End Date] (whichever is filled in) AND the Class End
Date > the [Dealer Need Date] or [Class Start Date] (again, whichever is
filled in). This may seem counter intuitive, but basically what you have is
the potential for 4 situations that could cause an availability conflict.

1. [Class Start Date] < [Dealer Need Date] AND [Class End Date] < [Dealer
Return Date]
2. [Class Start Date] < [Dealer Need Date] AND [Class End Date] > [Dealer
Return Date]
3. [Class Start Date] > [Dealer Need Date] AND [Class End Date] < [Dealer
Return Date]
4. [Class Start Date] > [Dealer Need Date] AND [Class End Date] > [Dealer
Return Date]

The comparison I used above will identify those records where any of these
potential conflicts exists. So, basically, I would create a criteria
string, then use the DCOUNT function to count the number of records (other
than the current one) which meet that criteria. If it is more than zero,
then you have a potential conflict. The down side of this is that you
cannot really do this comparison until you know both the ClassStartDate and
ClassEndDate or DealerNeedDate and DealerReturnDate, so you would need to
put this in the forms Before Update event; something like:

Private sub Form_BeforeUpdate(Cancel as integer)

Dim strCriteria as string

'Put some tests in here to make sure that both ClassStart and End or
DealerNeed and Return dates are entered

If NOT ISNULL(me.txt_Class_Start_Date) then
strCriteria = "[TrainingItemID] = " & me.cbo_TrainingItemID _
& " AND #" & me.txt_Class_Start_Date & "# <
NZ([Dealer Return Date], [Class End Date]" _
& " AND #" & me.txt_Class_End_Date & "# > NZ([Dealer
Need Date], [Class Start Date]"
Else
strCriteria = "[TrainingItemID] = " & me.cbo_TrainingItemID _
& " AND #" & me.txt_Dealer_Need_Date & "# <
NZ([Dealer Return Date], [Class End Date]" _
& " AND #" & me.txt_Dealer_Return_Date & "# >
NZ([Dealer Need Date], [Class Start Date]"
End if
Debug.print strCriteria
if Dcount("ID", "yourTableName", strCriteria) > 0 then
msgbox "This period conflicts with a previously scheduled event.", _
vbcritical + vbOKOnly, _
"The item you requested is not available
Cancel = true
end if

End Sub

Instead of testing against zero (0), the If statement that includes the
DCOUNT could also test against the number of the particular training item
you are looking for, something like:

IF Dcount("ID", "yourTableName", strCriteria) >= DLOOKUP("InStock",
"tblTrainingItems", "TrainingItemID = " & me.cbo_TrainingItemID) Then

HTH
Dale

I think I figured it out because it seems to be working. Cleaning up the
code

[quoted text clipped - 53 lines]

 

[croth68 via AccessMonster.com]

I am doing something wrong and I am just not seeing it. I keep getting an
error 3075. I am using the suggested code found in the previous post on the
thread. After the error it higlights this line If DCount("Combo52", "Table1",
[strCriteria]) > 0 Then

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strCriteria As String

If Not IsNull(Me.Class_Start_Date) Then
strCriteria = "[Combo52]= " & Me.Combo52 & " AND #" & Me.Class_Start_Date &
"# < NZ([Date Will Return], [Class End Date]" & " AND #" & Me.Class_End_Date
& "# > NZ([Date Needed], [Class Start Date]"
Else
strCriteria = "[Combo52]= " & Me.Combo52 & " AND #" & Me.Date_Needed & "# <
NZ([Date Will Return], [Class End Date]" & " AND #" & Me.Date_Will_Return &
"# > NZ([Date Needed], [Class Start Date]"
End If

Debug.Print strCriteria
If DCount("Combo52", "Table1", [strCriteria]) > 0 Then
MsgBox "Training Aid is already Scheduled", vbCritical + vbOKOnly, "Training
Aid unavailable during this time frame"
Cancel = True
End If

End Sub

Dale,

Thanks for getting back to me. I will warn you up front I am programming
stupid, I have been able to do what I have done so far really from this forum
and from knowledgeable people like yourself. The last programming I was
exposed to was very simple C++, which was 7 years ago or so, some of the
rules are coming back but sometimes they are the wrong rules, lol.

You are correct that the information will be added to the same record as
separate fields, there will be either a class start date etc or dealer need
date etc (you are also correct on the either/or) there will be an in date
field and an out date field that will be filled in regardless of weather it
is a dealer needing the training aid or a class needing the training aid. I
should be able to add an If Not IsNull(Date In) Then statement to the code.
If I am thinking right this should count all the records for training aid x
and if there is no date filled in for Date in then should list that training
aid as unavailable which would be a msgbox command if I am not mistaken.
Thanks for the help on this I appreciate it, my email is c_r_o_t_h_6_8@c_o_x.
net. I tried to code the my email to keep spammers away, take away all of the
underscores and there you go. Thanks again for the help.

The reason the first change worked is that your [Dealer Zip] field is a text
data type, not a number. Whenever you build SQL strings on the fly like

[quoted text clipped - 82 lines]

 

[Dale Fye]

Is [Combo52] a field in your data table? Normally that name would denote a
combo box on a form.

Replace the bracketed Combo52 [Combo52] with the name of the field in your
table, that you want to compare to the value of Combo52.

The first strCriteria should look like:

strCriteria = "[FieldName]= " & Me.Combo52 _
& " AND #" & Me.Class_Start_Date & "# < NZ([Date Will
Return], [Class End Date]" _
& " AND #" & Me.Class_End_Date & "# > NZ([Date Needed],
[Class Start Date]"

You will need to make the same correction in the second part of the IF Then
statement.

The DCOUNT function also need to reference a field in the table, not the
combo box, so it should look something like:

DCOUNT("FieldName", "Table1", strCriteria)

Note that there are no brackets around strCriteria.

HTH
Dale

I am doing something wrong and I am just not seeing it. I keep getting an
error 3075. I am using the suggested code found in the previous post on
the
thread. After the error it higlights this line If DCount("Combo52",
"Table1",
[strCriteria]) > 0 Then

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strCriteria As String

If Not IsNull(Me.Class_Start_Date) Then
strCriteria = "[Combo52]= " & Me.Combo52 & " AND #" & Me.Class_Start_Date
&
"# < NZ([Date Will Return], [Class End Date]" & " AND #" &
Me.Class_End_Date
& "# > NZ([Date Needed], [Class Start Date]"
Else
strCriteria = "[Combo52]= " & Me.Combo52 & " AND #" & Me.Date_Needed & "#
<
NZ([Date Will Return], [Class End Date]" & " AND #" & Me.Date_Will_Return
&
"# > NZ([Date Needed], [Class Start Date]"
End If

Debug.Print strCriteria
If DCount("Combo52", "Table1", [strCriteria]) > 0 Then
MsgBox "Training Aid is already Scheduled", vbCritical + vbOKOnly,
"Training
Aid unavailable during this time frame"
Cancel = True
End If

End Sub

Dale,

Thanks for getting back to me. I will warn you up front I am programming
stupid, I have been able to do what I have done so far really from this
forum
and from knowledgeable people like yourself. The last programming I was
exposed to was very simple C++, which was 7 years ago or so, some of the
rules are coming back but sometimes they are the wrong rules, lol.

You are correct that the information will be added to the same record as
separate fields, there will be either a class start date etc or dealer
need
date etc (you are also correct on the either/or) there will be an in date
field and an out date field that will be filled in regardless of weather
it
is a dealer needing the training aid or a class needing the training aid.
I
should be able to add an If Not IsNull(Date In) Then statement to the
code.
If I am thinking right this should count all the records for training aid
x
and if there is no date filled in for Date in then should list that
training
aid as unavailable which would be a msgbox command if I am not mistaken.
Thanks for the help on this I appreciate it, my email is
c_r_o_t_h_6_8@c_o_x.
net. I tried to code the my email to keep spammers away, take away all of
the
underscores and there you go. Thanks again for the help.

The reason the first change worked is that your [Dealer Zip] field is a
text
data type, not a number. Whenever you build SQL strings on the fly like

[quoted text clipped - 82 lines]

as soon as I get the code cleaned up. I am very new to programming so
your
suggestions help tremendously.

 

[croth68 via AccessMonster.com]

Dale Thanks for getting back to me, I have tried the changes as you suggested
and made some progress however I am at another brick wall (I apologize, I am
programming stupid). I am getting a runtime error 13 Type Mismatch and am
unable to figure out why? Combo52 is the control on my form where I can
select which training aid to input. This might have something to do with my
problem but the values for combo52 are pulled from the training aids table.
Here is the code.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strCriteria As String

If Not IsNull(Me.Class_Start_Date) Then
strCriteria = "[Training Aid ID] = " & Me.Combo52 & " AND #" & Me.
Class_Start_Date & "# < NZ([Date Will Return], [Class End Date]" & " AND #" &
Me.Class_End_Date & "# > NZ([Date Needed], [Class Start Date]"
Else
strCriteria = "[Training Aid ID] = " & Me.Combo52 & " AND #" & Me.Date_Needed
& "# < NZ([Date Will Return], [Class End Date]" & " AND #" & Me.
Date_Will_Return & "# > NZ([Date Needed], [Class Start Date]"
End If

Debug.Print strCriteria
If DCount([Training Aid ID], "Table1", strCriteria > 0) Then
MsgBox "Training Aid is already Scheduled", vbCritical + vbOKOnly, "Training
Aid unavailable during this time frame"
Cancel = True
End If

End Sub

Thanks for all your help on this I really appreciate it.

 

[Dale Fye]

1. I noticed while I was reformatting the text below that you had dropped
the ) off of the NZ( ) function. If you add that back in, it might work
properly.

2. If not, then:

Confirm the data types of the 5 fields ([Training Aid ID], [Date Will
Return], [Class End Date], [Date Needed], [Class Start Date]) in your table
..

What is the record source for Combo52? Does it have one or more columns?
What are the column names and data types? What is the bound column.
The bound column in Combo52 must match the data type of the field [Training
Aid ID]. If they are both numeric, then the code below should work for that
part of the query. However if they are text, then you need to wrap the
value of me.combo52 in quotes (I'll use apostrophes, so look closely). I'm
also going to add some line breaks to make it easier to read in the news
browser.

If Not IsNull(Me.Class_Start_Date) Then
strCriteria = "[Training Aid ID] = '" & Me.Combo52 & "'" _
& " AND #" & Me.Class_Start_Date & "#" _
& " < NZ([Date Will Return], [Class End Date]) " _
& " AND #" & Me.Class_End_Date & "#" _
& " > NZ([Date Needed], [Class Start Date])"
Else
strCriteria = "[Training Aid ID] = '" & Me.Combo52 & "'" _
& " AND #" & Me.Date_Needed & "#" _
& " < NZ([Date Will Return], [Class End Date])" _
& " AND #" & Me.Date_Will_Return & "#" _
& " > NZ([Date Needed], [Class Start Date])"
End If

HTH
Dale

Dale Thanks for getting back to me, I have tried the changes as you
suggested
and made some progress however I am at another brick wall (I apologize, I
am
programming stupid). I am getting a runtime error 13 Type Mismatch and am
unable to figure out why? Combo52 is the control on my form where I can
select which training aid to input. This might have something to do with
my
problem but the values for combo52 are pulled from the training aids
table.
Here is the code.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strCriteria As String

If Not IsNull(Me.Class_Start_Date) Then
strCriteria = "[Training Aid ID] = " & Me.Combo52 & " AND #" & Me.
Class_Start_Date & "# < NZ([Date Will Return], [Class End Date]" & " AND
#" &
Me.Class_End_Date & "# > NZ([Date Needed], [Class Start Date]"
Else
strCriteria = "[Training Aid ID] = " & Me.Combo52 & " AND #" &
Me.Date_Needed
& "# < NZ([Date Will Return], [Class End Date]" & " AND #" & Me.
Date_Will_Return & "# > NZ([Date Needed], [Class Start Date]"
End If

Debug.Print strCriteria
If DCount([Training Aid ID], "Table1", strCriteria > 0) Then
MsgBox "Training Aid is already Scheduled", vbCritical + vbOKOnly,
"Training
Aid unavailable during this time frame"
Cancel = True
End If

End Sub

Thanks for all your help on this I really appreciate it.