ID mixing with all records

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all,
I am having an issue with my form when I apply a filter. The filter is
working as expected, until you look at the KeyID or the UtilityAlias (Company
Name). The problem is that when you run this code:

Private Sub cboStreetNames_AfterUpdate()
' Find the record that matches the control.

On Error GoTo cboStreetNames_AfterUpdate_Err

If chkAllUT = True Then
DoCmd.ShowAllRecords
Me.cboStreetNames.Requery
DoCmd.ApplyFilter "qSearchStreet"
Me.cboStreetNames.Requery
Else
DoCmd.ShowAllRecords
Me.cboStreetNames.Requery
DoCmd.ApplyFilter "qSearchStreet", "JobID = " & Me.JobID
Me.cboStreetNames.Requery
End If

cboStreetNames = ""
chkAllUT = False
DoCmd.GoToControl "cboStreetNames"

cboStreetNames_AfterUpdate_Exit:
Exit Sub

cboStreetNames_AfterUpdate_Err:
MsgBox Error$
Resume cboStreetNames_AfterUpdate_Exit

End Sub

If chkAllUT = True then it should show everything, which it does, but with
the wrong KeyID.

This is where I kinda suspect the problem to be:

Private Sub Form_Current()

If Not IsNull(Me.OpenArgs) Then
Me.JobID = Me.OpenArgs
End If
....
But when I remove this code it still does the same thing.

my combo box filters against several fields:

SELECT tblCuts.Address1, tblCuts.StreetName, tblCuts.From, tblCuts.To,
tblUtilities.UtilityAlias, tblCuts.JobID, *
FROM tblUtilities INNER JOIN tblCuts ON tblUtilities.KeyID = tblCuts.JobID
WHERE ((([Forms].[fCuts].[cboStreetNames]) Is Null)) OR (((tblCuts.Address1)
Like "*" & [Forms].[fCuts].[cboStreetNames] & "*")) OR (((tblCuts.StreetName)
Like "*" & [Forms].[fCuts].[cboStreetNames] & "*")) OR (((tblCuts.From) Like
"*" & [Forms].[fCuts].[cboStreetNames] & "*")) OR (((tblCuts.To) Like "*" &
[Forms].[fCuts].[cboStreetNames] & "*"))
ORDER BY tblCuts.DateCalled DESC;

Sorry, a lot to take in.
Please help
Thanks,
NickX
 
Jeff,
Thank you for your response.
tblCuts is where I am filtering my records JobID(Many) is a Long Integer
that corresponds to KeyID(One) (AutoNumber/Primary Key) in tblUtilities.
tblUtilities is where my parent records are stored (Companies), this data
rarely changes and is the used to display the name of the company on the
form. tblCuts is where most of the daily data entry is stored.
This is the SQL that my form is bound to:

SELECT tblUtilities.KeyID, tblCuts.JobID, tblCuts.DateCalled,
tblCuts.StreetName, *
FROM tblUtilities INNER JOIN tblCuts ON tblUtilities.KeyID = tblCuts.JobID
ORDER BY tblCuts.DateCalled;

chkAllUT is an Unbound check box whose default value is "False"

Hope this is what you were asking for, I seem to be at a loss for words today.
Thanks,
NickX


Jeff Boyce said:
Nick

You've described the "how" ... now, how 'bout the "what". Since queries and
forms and reports are all based on the underlying data, please describe your
table structure and data types.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

Nick X said:
Hi all,
I am having an issue with my form when I apply a filter. The filter is
working as expected, until you look at the KeyID or the UtilityAlias (Company
Name). The problem is that when you run this code:

Private Sub cboStreetNames_AfterUpdate()
' Find the record that matches the control.

On Error GoTo cboStreetNames_AfterUpdate_Err

If chkAllUT = True Then
DoCmd.ShowAllRecords
Me.cboStreetNames.Requery
DoCmd.ApplyFilter "qSearchStreet"
Me.cboStreetNames.Requery
Else
DoCmd.ShowAllRecords
Me.cboStreetNames.Requery
DoCmd.ApplyFilter "qSearchStreet", "JobID = " & Me.JobID
Me.cboStreetNames.Requery
End If

cboStreetNames = ""
chkAllUT = False
DoCmd.GoToControl "cboStreetNames"

cboStreetNames_AfterUpdate_Exit:
Exit Sub

cboStreetNames_AfterUpdate_Err:
MsgBox Error$
Resume cboStreetNames_AfterUpdate_Exit

End Sub

If chkAllUT = True then it should show everything, which it does, but with
the wrong KeyID.

This is where I kinda suspect the problem to be:

Private Sub Form_Current()

If Not IsNull(Me.OpenArgs) Then
Me.JobID = Me.OpenArgs
End If
...
But when I remove this code it still does the same thing.

my combo box filters against several fields:

SELECT tblCuts.Address1, tblCuts.StreetName, tblCuts.From, tblCuts.To,
tblUtilities.UtilityAlias, tblCuts.JobID, *
FROM tblUtilities INNER JOIN tblCuts ON tblUtilities.KeyID = tblCuts.JobID
WHERE ((([Forms].[fCuts].[cboStreetNames]) Is Null)) OR (((tblCuts.Address1)
Like "*" & [Forms].[fCuts].[cboStreetNames] & "*")) OR (((tblCuts.StreetName)
Like "*" & [Forms].[fCuts].[cboStreetNames] & "*")) OR (((tblCuts.From) Like
"*" & [Forms].[fCuts].[cboStreetNames] & "*")) OR (((tblCuts.To) Like "*" &
[Forms].[fCuts].[cboStreetNames] & "*"))
ORDER BY tblCuts.DateCalled DESC;

Sorry, a lot to take in.
Please help
Thanks,
NickX
Click to expand...
Click to expand...
 
OK, all the answer are staring me in the face today, and all I can do is
stare blankly back. The problem was with the :
Private Sub Form_Current()

If Not IsNull(Me.OpenArgs) Then
Me.JobID = Me.OpenArgs
End If

End Sub
I just didn't take the time to re-open the form after I commented out the
code.

Is it possible to programmatically remove the openargs?
Thanks,
NickX
Jeff Boyce said:
Nick

You've described the "how" ... now, how 'bout the "what". Since queries and
forms and reports are all based on the underlying data, please describe your
table structure and data types.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

Nick X said:
Hi all,
I am having an issue with my form when I apply a filter. The filter is
working as expected, until you look at the KeyID or the UtilityAlias (Company
Name). The problem is that when you run this code:

Private Sub cboStreetNames_AfterUpdate()
' Find the record that matches the control.

On Error GoTo cboStreetNames_AfterUpdate_Err

If chkAllUT = True Then
DoCmd.ShowAllRecords
Me.cboStreetNames.Requery
DoCmd.ApplyFilter "qSearchStreet"
Me.cboStreetNames.Requery
Else
DoCmd.ShowAllRecords
Me.cboStreetNames.Requery
DoCmd.ApplyFilter "qSearchStreet", "JobID = " & Me.JobID
Me.cboStreetNames.Requery
End If

cboStreetNames = ""
chkAllUT = False
DoCmd.GoToControl "cboStreetNames"

cboStreetNames_AfterUpdate_Exit:
Exit Sub

cboStreetNames_AfterUpdate_Err:
MsgBox Error$
Resume cboStreetNames_AfterUpdate_Exit

End Sub

If chkAllUT = True then it should show everything, which it does, but with
the wrong KeyID.

This is where I kinda suspect the problem to be:

Private Sub Form_Current()

If Not IsNull(Me.OpenArgs) Then
Me.JobID = Me.OpenArgs
End If
...
But when I remove this code it still does the same thing.

my combo box filters against several fields:

SELECT tblCuts.Address1, tblCuts.StreetName, tblCuts.From, tblCuts.To,
tblUtilities.UtilityAlias, tblCuts.JobID, *
FROM tblUtilities INNER JOIN tblCuts ON tblUtilities.KeyID = tblCuts.JobID
WHERE ((([Forms].[fCuts].[cboStreetNames]) Is Null)) OR (((tblCuts.Address1)
Like "*" & [Forms].[fCuts].[cboStreetNames] & "*")) OR (((tblCuts.StreetName)
Like "*" & [Forms].[fCuts].[cboStreetNames] & "*")) OR (((tblCuts.From) Like
"*" & [Forms].[fCuts].[cboStreetNames] & "*")) OR (((tblCuts.To) Like "*" &
[Forms].[fCuts].[cboStreetNames] & "*"))
ORDER BY tblCuts.DateCalled DESC;

Sorry, a lot to take in.
Please help
Thanks,
NickX
Click to expand...
Click to expand...
 
Nick

Still seems like a "how" question ... how to programmatically remove the
OpenArgs. Please describe the "what"... what is it that you are trying to
accomplish. I ask because there may be more than one way to get your job
done...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Nick X said:
OK, all the answer are staring me in the face today, and all I can do is
stare blankly back. The problem was with the :
Private Sub Form_Current()

If Not IsNull(Me.OpenArgs) Then
Me.JobID = Me.OpenArgs
End If

End Sub
I just didn't take the time to re-open the form after I commented out the
code.

Is it possible to programmatically remove the openargs?
Thanks,
NickX
Jeff Boyce said:
Nick

You've described the "how" ... now, how 'bout the "what". Since queries
and
forms and reports are all based on the underlying data, please describe
your
table structure and data types.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

Nick X said:
Hi all,
I am having an issue with my form when I apply a filter. The filter is
working as expected, until you look at the KeyID or the UtilityAlias (Company
Name). The problem is that when you run this code:

Private Sub cboStreetNames_AfterUpdate()
' Find the record that matches the control.

On Error GoTo cboStreetNames_AfterUpdate_Err

If chkAllUT = True Then
DoCmd.ShowAllRecords
Me.cboStreetNames.Requery
DoCmd.ApplyFilter "qSearchStreet"
Me.cboStreetNames.Requery
Else
DoCmd.ShowAllRecords
Me.cboStreetNames.Requery
DoCmd.ApplyFilter "qSearchStreet", "JobID = " & Me.JobID
Me.cboStreetNames.Requery
End If

cboStreetNames = ""
chkAllUT = False
DoCmd.GoToControl "cboStreetNames"

cboStreetNames_AfterUpdate_Exit:
Exit Sub

cboStreetNames_AfterUpdate_Err:
MsgBox Error$
Resume cboStreetNames_AfterUpdate_Exit

End Sub

If chkAllUT = True then it should show everything, which it does, but
with
the wrong KeyID.

This is where I kinda suspect the problem to be:

Private Sub Form_Current()

If Not IsNull(Me.OpenArgs) Then
Me.JobID = Me.OpenArgs
End If
...
But when I remove this code it still does the same thing.

my combo box filters against several fields:

SELECT tblCuts.Address1, tblCuts.StreetName, tblCuts.From, tblCuts.To,
tblUtilities.UtilityAlias, tblCuts.JobID, *
FROM tblUtilities INNER JOIN tblCuts ON tblUtilities.KeyID =
tblCuts.JobID
WHERE ((([Forms].[fCuts].[cboStreetNames]) Is Null)) OR (((tblCuts.Address1)
Like "*" & [Forms].[fCuts].[cboStreetNames] & "*")) OR (((tblCuts.StreetName)
Like "*" & [Forms].[fCuts].[cboStreetNames] & "*")) OR (((tblCuts.From) Like
"*" & [Forms].[fCuts].[cboStreetNames] & "*")) OR (((tblCuts.To) Like
"*" &
[Forms].[fCuts].[cboStreetNames] & "*"))
ORDER BY tblCuts.DateCalled DESC;

Sorry, a lot to take in.
Please help
Thanks,
NickX
Click to expand...
Click to expand...
Click to expand...
 
My combo box, when combine with the check box; should display all records
that match the like statement from the combo/query-filter. Without the check
box the filter should only display the matches that have the same
UtilityAlias(KeyID) as what is displayed on the form. Actually, I'm not sure
that's the important issue anymore. I have removed:
If Not IsNull(Me.OpenArgs) Then
Me.JobID = Me.OpenArgs
End If
...and it works like it should.
Is it possible, though, that by setting Me.JobID = Me.OpenArgs in the
OnCurrent event that I could have inadvertently changed the value of [JobID]?
I do have some rogue values that are not matching up like they should.

Thanks,
NickX
Jeff Boyce said:
Nick

Still seems like a "how" question ... how to programmatically remove the
OpenArgs. Please describe the "what"... what is it that you are trying to
accomplish. I ask because there may be more than one way to get your job
done...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Nick X said:
OK, all the answer are staring me in the face today, and all I can do is
stare blankly back. The problem was with the :
Private Sub Form_Current()

If Not IsNull(Me.OpenArgs) Then
Me.JobID = Me.OpenArgs
End If

End Sub
I just didn't take the time to re-open the form after I commented out the
code.

Is it possible to programmatically remove the openargs?
Thanks,
NickX
Jeff Boyce said:
Nick

You've described the "how" ... now, how 'bout the "what". Since queries
and
forms and reports are all based on the underlying data, please describe
your
table structure and data types.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

Hi all,
I am having an issue with my form when I apply a filter. The filter is
working as expected, until you look at the KeyID or the UtilityAlias
(Company
Name). The problem is that when you run this code:

Private Sub cboStreetNames_AfterUpdate()
' Find the record that matches the control.

On Error GoTo cboStreetNames_AfterUpdate_Err

If chkAllUT = True Then
DoCmd.ShowAllRecords
Me.cboStreetNames.Requery
DoCmd.ApplyFilter "qSearchStreet"
Me.cboStreetNames.Requery
Else
DoCmd.ShowAllRecords
Me.cboStreetNames.Requery
DoCmd.ApplyFilter "qSearchStreet", "JobID = " & Me.JobID
Me.cboStreetNames.Requery
End If

cboStreetNames = ""
chkAllUT = False
DoCmd.GoToControl "cboStreetNames"

cboStreetNames_AfterUpdate_Exit:
Exit Sub

cboStreetNames_AfterUpdate_Err:
MsgBox Error$
Resume cboStreetNames_AfterUpdate_Exit

End Sub

If chkAllUT = True then it should show everything, which it does, but
with
the wrong KeyID.

This is where I kinda suspect the problem to be:

Private Sub Form_Current()

If Not IsNull(Me.OpenArgs) Then
Me.JobID = Me.OpenArgs
End If
...
But when I remove this code it still does the same thing.

my combo box filters against several fields:

SELECT tblCuts.Address1, tblCuts.StreetName, tblCuts.From, tblCuts.To,
tblUtilities.UtilityAlias, tblCuts.JobID, *
FROM tblUtilities INNER JOIN tblCuts ON tblUtilities.KeyID =
tblCuts.JobID
WHERE ((([Forms].[fCuts].[cboStreetNames]) Is Null)) OR
(((tblCuts.Address1)
Like "*" & [Forms].[fCuts].[cboStreetNames] & "*")) OR
(((tblCuts.StreetName)
Like "*" & [Forms].[fCuts].[cboStreetNames] & "*")) OR (((tblCuts.From)
Like
"*" & [Forms].[fCuts].[cboStreetNames] & "*")) OR (((tblCuts.To) Like
"*"
&
[Forms].[fCuts].[cboStreetNames] & "*"))
ORDER BY tblCuts.DateCalled DESC;

Sorry, a lot to take in.
Please help
Thanks,
NickX
Click to expand...
Click to expand...
Click to expand...
 
Nick

I suspect we're not talking about the same subjects.

If you are saying that your form is returning spurious results, try opening
it in design view and checking the properties of the form to see if a filter
has been set.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Nick X said:
My combo box, when combine with the check box; should display all records
that match the like statement from the combo/query-filter. Without the
check
box the filter should only display the matches that have the same
UtilityAlias(KeyID) as what is displayed on the form. Actually, I'm not
sure
that's the important issue anymore. I have removed:
If Not IsNull(Me.OpenArgs) Then
Me.JobID = Me.OpenArgs
End If
..and it works like it should.
Is it possible, though, that by setting Me.JobID = Me.OpenArgs in the
OnCurrent event that I could have inadvertently changed the value of
[JobID]?
I do have some rogue values that are not matching up like they should.

Thanks,
NickX
Jeff Boyce said:
Nick

Still seems like a "how" question ... how to programmatically remove the
OpenArgs. Please describe the "what"... what is it that you are trying
to
accomplish. I ask because there may be more than one way to get your job
done...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Nick X said:
OK, all the answer are staring me in the face today, and all I can do
is
stare blankly back. The problem was with the :
Private Sub Form_Current()

If Not IsNull(Me.OpenArgs) Then
Me.JobID = Me.OpenArgs
End If

End Sub
I just didn't take the time to re-open the form after I commented out
the
code.

Is it possible to programmatically remove the openargs?
Thanks,
NickX
:

Nick

You've described the "how" ... now, how 'bout the "what". Since
queries
and
forms and reports are all based on the underlying data, please
describe
your
table structure and data types.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

Hi all,
I am having an issue with my form when I apply a filter. The filter
is
working as expected, until you look at the KeyID or the UtilityAlias
(Company
Name). The problem is that when you run this code:

Private Sub cboStreetNames_AfterUpdate()
' Find the record that matches the control.

On Error GoTo cboStreetNames_AfterUpdate_Err

If chkAllUT = True Then
DoCmd.ShowAllRecords
Me.cboStreetNames.Requery
DoCmd.ApplyFilter "qSearchStreet"
Me.cboStreetNames.Requery
Else
DoCmd.ShowAllRecords
Me.cboStreetNames.Requery
DoCmd.ApplyFilter "qSearchStreet", "JobID = " & Me.JobID
Me.cboStreetNames.Requery
End If

cboStreetNames = ""
chkAllUT = False
DoCmd.GoToControl "cboStreetNames"

cboStreetNames_AfterUpdate_Exit:
Exit Sub

cboStreetNames_AfterUpdate_Err:
MsgBox Error$
Resume cboStreetNames_AfterUpdate_Exit

End Sub

If chkAllUT = True then it should show everything, which it does,
but
with
the wrong KeyID.

This is where I kinda suspect the problem to be:

Private Sub Form_Current()

If Not IsNull(Me.OpenArgs) Then
Me.JobID = Me.OpenArgs
End If
...
But when I remove this code it still does the same thing.

my combo box filters against several fields:

SELECT tblCuts.Address1, tblCuts.StreetName, tblCuts.From,
tblCuts.To,
tblUtilities.UtilityAlias, tblCuts.JobID, *
FROM tblUtilities INNER JOIN tblCuts ON tblUtilities.KeyID =
tblCuts.JobID
WHERE ((([Forms].[fCuts].[cboStreetNames]) Is Null)) OR
(((tblCuts.Address1)
Like "*" & [Forms].[fCuts].[cboStreetNames] & "*")) OR
(((tblCuts.StreetName)
Like "*" & [Forms].[fCuts].[cboStreetNames] & "*")) OR
(((tblCuts.From)
Like
"*" & [Forms].[fCuts].[cboStreetNames] & "*")) OR (((tblCuts.To)
Like
"*"
&
[Forms].[fCuts].[cboStreetNames] & "*"))
ORDER BY tblCuts.DateCalled DESC;

Sorry, a lot to take in.
Please help
Thanks,
NickX
Click to expand...
Click to expand...
Click to expand...
 
Jeff,
I have a standard combo box that is used as a filter. When I typed
something into the combo box and hit tab or enter it would return the
filtered values I expected. The issue was that when it returned the filtered
information, and having the following:
Private Sub Form_Current()

If Not IsNull(Me.OpenArgs) Then
Me.JobID = Me.OpenArgs
End If

End Sub

If the JobID=1 before the filter was applied it continued to show as 1 as I
went through each record, even if other information pointed to the fact that
the JobID should 2 or 6, etc.

I have removed the code from the OnCurrent event and now I am getting the
results I desire.

My next question is, by having the OnCurrent event set to Me.JobID =
Me.OpenArgs, is it possible that the JobID of some of the filtered records
could have been changed to match the intial JobID.

Thank you for your time,
NickX
 
Nick

I'm at a loss to guess what is happening. Perhaps other newsgroup readers
can offer a new perspective.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Back
Top