Cbo running of a query

  • Thread starter Thread starter Steven M. Britton
  • Start date Start date
S

Steven M. Britton

I have three combo boxes the first has a select query that
gets is info from a table containing all of my customers.
The second box runs a select query from the same table,
but has the critera of the customer number in the first
combo box and the third does the same with the critera of
the first and second combo box.

The problem that I am having is that after I select a
value in the first box the second and third run fine, but
if I change the value in the first box the second and
third do not update their values. How can I correct
this? Any Ideas?
 
Steven M. Britton said:
I have three combo boxes the first has a select query that
gets is info from a table containing all of my customers.
The second box runs a select query from the same table,
but has the critera of the customer number in the first
combo box and the third does the same with the critera of
the first and second combo box.

The problem that I am having is that after I select a
value in the first box the second and third run fine, but
if I change the value in the first box the second and
third do not update their values. How can I correct
this? Any Ideas?
Click to expand...

You need code in the AfterUpdate event of the first combo box that
requeries both the second and third combo boxes. For example,

Private Sub Combo1_AfterUpdate()
Me.Combo2.Requery
Me.Combo3.Requery
End Sub

You also need code in the AfterUpdate event of the second combo box that
requeries just the third combo box.
 
I added the code that was suggested, however I have
required fields on the form so when I get to the first
combo box and select the account number it requeries.
Then I get an error message saying that I can't continue
because of the other required fields. Does that make
since? FYI every field on the form is required before I
can save the record. I currently have this code (shorten
for post) in the BeforeUpdate Event for the form.

Private Sub Form_BeforeUpdate(Cancel As Integer)
' Checks to make sure that all required fields do not
have Null Values

Dim strMsg As String
Dim bWarn As Boolean
Dim strField As String

If IsNull(Me.cmbEmployeeID) = True Or Me!cmbEmployeeID
= "" Then
Cancel = True
strMsg = strMsg & "Employee ID Required." & vbCrLf
strField = "cmbEmployeeID"
End If

If Cancel Then
strMsg = strMsg & "Enter the data, or Press <Esc>
to undo."
MsgBox strMsg, , "Invalid data"
Else
'Do your warnings.
If IsNull(Me.cmbEmployeeID) = True Or Me!
cmbEmployeeID = "" Then
bWarn = True
strMsg = strMsg & "Employee ID Required." &
vbCrLf
strField = "cmbEmployeeID"
End If

If bWarn Then
strMsg = strMsg & vbCrLf & "Proceed anyway?"
If MsgBox(strMsg, vbYesNo +
vbDefaultButton2, "Warning") <> vbYes Then
Cancel = True
End If
End If
End If

'Set focus to the problem field.
If Cancel And Len(strField) > 0 Then
Me(strField).SetFocus
End If

Me.TOTALHOURS = Me.TotalTime1
Me.CENTER_NO = Me.CLIENTsubform.Form!CENTER_NO
Me.DEPT_NO = Me.CLIENTsubform.Form!DEPT_NO
Me.JOB_CODE = Me.RATETABL_subform1.Form!JOB_CODE
Me.PIECERATE = Me.RATETABL_subform1.Form!PIECERATE
Me.PRVL_RATE = Me.RATETABL_subform1.Form!PRVL_RATE
Me.AMT_EARN = (Me.NO_ACCEPTD - Me.NO_REJECTD)* me.PIECERATE
 
Steven M. Britton said:
I added the code that was suggested, however I have
required fields on the form so when I get to the first
combo box and select the account number it requeries.
Then I get an error message saying that I can't continue
because of the other required fields. Does that make
since? FYI every field on the form is required before I
can save the record.
Click to expand...

That doesn't make sense to me. Why should requerying the combo boxes
force your form to attempt to save the record? You didn't slip up and
write "Me.Requery" when you meant to write "Me.ComboBoxName.Requery",
did you? I think you'd better post the code you have in the combo
boxes' AfterUpdate events.
 
In the AfterUpdate I also had code setting the value to
Null for the second and third combo boxes. That is what
was causing the error, however after I remove this code I
am receiving a different error. The combo boxes do update
themselves to the current customer account, but if the I
selected a customer account 1897 which as in combo box 2
Job codes 1 - 24 and in combo box 3 Steps 1-7 ; then if I
change the customer account combo 1 to 3537 which has job
codes 1-7 and steps combo 3 for that... If I originally
had the job 22 selected from the first customer, the new
account 3537 doesn't have that job number and if I attempt
to save the record I get an error saying that the value I
am trying to set is invalid and I am asked to debug the
code. However if I do change the second and third combo I
can save the records correctly. I am posting all of the
code and I hope my explaination of the problem is
solvable....


Private Sub cmbCust_Acct_AfterUpdate()
Me.cmbJob_No.Requery
Me.cmbStep_No.Requery
End Sub

Private Sub cmbCust_Acct_NotInList(NewData As String,
Response As Integer)

strMsg = "You have entered an Customer Account Number
that does not exist " & _
"insure you have the entered the correct Customer
Account Number " & _
"if the Customer Account Number is correct and not
available on this list " & _
"you will need to add them to the Customer Table"
strTitle = "Invalid Customer Account Number"
intStyle = vbOKOnly
MsgBox strMsg, intStyle, strTitle
Me.cmbCust_Acct.SetFocus
Response = acDataErrContinue

End Sub

Private Sub cmbEmployeeID_NotInList(NewData As String,
Response As Integer)

strMsg = "You have entered an Employee ID that does
not exist " & _
"insure you have the entered the correct Employee
ID " & _
"if the Employee ID is correct and not available
on this list " & _
"you will need to add them to the Client Table"
strTitle = "Invalid Employee ID"
intStyle = vbOKOnly
MsgBox strMsg, intStyle, strTitle
Me.cmbEmployeeID.SetFocus
Response = acDataErrContinue

End Sub

Private Sub cmbJob_No_AfterUpdate()
Me.cmbStep_No.Requery
End Sub

Private Sub cmbJob_No_NotInList(NewData As String,
Response As Integer)

strMsg = "You have entered an Job Number that does not
exist " & _
"insure you have the entered the correct Job
Number " & _
"if the Job Number is correct and not available on
this list " & _
"you will need to add the Job to the Rate Table"
strTitle = "Invalid Job Number"
intStyle = vbOKOnly
MsgBox strMsg, intStyle, strTitle
Me.cmbJob_No.SetFocus
Response = acDataErrContinue

End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
' Checks to make sure that all required fields do not
have Null Values

Dim strMsg As String
Dim bWarn As Boolean
Dim strField As String

If IsNull(Me.cmbEmployeeID) = True Or Me!cmbEmployeeID
= "" Then
Cancel = True
strMsg = strMsg & "Employee ID Required." & vbCrLf
strField = "cmbEmployeeID"
Else
Me.CENTER_NO = Me.CLIENTsubform.Form!CENTER_NO
Me.DEPT_NO = Me.CLIENTsubform.Form!DEPT_NO
End If
If IsNull(Me.cmbCust_Acct) = True Or Me!cmbCust_Acct
= "" Then
Cancel = True
strMsg = strMsg & "Customer Account Number
Required." & vbCrLf
strField = "cmbCust_Acct"
End If
If IsNull(Me.cmbJob_No) = True Or Me!cmbJob_No = ""
Then
Cancel = True
strMsg = strMsg & "Job Number Required." & vbCrLf
strField = "cmbJob_no"
End If
If IsNull(Me.cmbStep_No) = True Or Me!cmbStep_No = ""
Then
Cancel = True
strMsg = strMsg & "Step Number Required." & vbCrLf
strField = "cmbStep_No"
Else
Me.JOB_CODE = Me.RATETABL_subform1.Form!JOB_CODE
End If
If IsNull(Me.DATEPERFMD) = True Or Me!DATEPERFMD = ""
Then
Cancel = True
strMsg = strMsg & "Date Performed Required." &
vbCrLf
strField = "dateperfmd"
End If
If IsNull(Me.[Start Time]) = True Or Me![Start Time]
= "" Then
Cancel = True
strMsg = strMsg & "Start Time Required." & vbCrLf
strField = "[Start Time]"
End If
If IsNull(Me.[Stop Time]) = True Or Me![Stop Time]
= "" Then
Cancel = True
strMsg = strMsg & "Stop Time Required." & vbCrLf
strField = "[Stop Time]"
Else
Me.TOTALHOURS = Me.TotalTime1
End If
If IsNull(Me.NO_ACCEPTD) = True Or Me!NO_ACCEPTD = ""
Then
Cancel = True
strMsg = strMsg & "Number of Accepted Units
Required." & vbCrLf
strField = "No_Acceptd"
Else
Me.PIECERATE = Me.RATETABL_subform1.Form!PIECERATE
Me.PRVL_RATE = Me.RATETABL_subform1.Form!PRVL_RATE
Me.AMT_EARN = (Me.NO_ACCEPTD - Me.NO_REJECTD) *
Me.PIECERATE
End If
If IsNull(Me.NO_REJECTD) = True Or Me!NO_REJECTD = ""
Then
Cancel = True
strMsg = strMsg & "Number of Rejected Units
Required." & vbCrLf
strField = "NO_REJECTD"
End If

If Cancel Then
strMsg = strMsg & "Enter the data, or Press <Esc>
to undo."
MsgBox strMsg, , "Invalid data"
Else
'Do your warnings.
If IsNull(Me.cmbEmployeeID) = True Or Me!
cmbEmployeeID = "" Then
bWarn = True
strMsg = strMsg & "Employee ID Required." &
vbCrLf
strField = "cmbEmployeeID"
End If
If IsNull(Me.cmbCust_Acct) = True Or Me!
cmbCust_Acct = "" Then
bWarn = True
strMsg = strMsg & "Customer Account Required."
& vbCrLf
strField = "cmbCust_Acct"
End If

If bWarn Then
strMsg = strMsg & vbCrLf & "Proceed anyway?"
If MsgBox(strMsg, vbYesNo +
vbDefaultButton2, "Warning") <> vbYes Then
Cancel = True
End If
End If
End If

'Set focus to the problem field.
If Cancel And Len(strField) > 0 Then
Me(strField).SetFocus
End If

End Sub
Private Sub Form_Open(Cancel As Integer)
Me.cmbEmployeeID.SetFocus
End Sub

Private Sub btnSaveRecord_Click()
On Error GoTo Err_btnSaveRecord_Click


DoCmd.DoMenuItem acFormBar, acRecordsMenu,
acSaveRecord, , acMenuVer70

Exit_btnSaveRecord_Click:
Exit Sub

Err_btnSaveRecord_Click:
MsgBox Err.Description
Resume Exit_btnSaveRecord_Click

End Sub


-----Original Message-----
message news:[email protected]

That doesn't make sense to me. Why should requerying the combo boxes
force your form to attempt to save the record? You didn't slip up and
write "Me.Requery" when you meant to
Click to expand...
write "Me.ComboBoxName.Requery",
 
Steven M. Britton said:
In the AfterUpdate I also had code setting the value to
Null for the second and third combo boxes. That is what
was causing the error, however after I remove this code I
am receiving a different error. The combo boxes do update
themselves to the current customer account, but if the I
selected a customer account 1897 which as in combo box 2
Job codes 1 - 24 and in combo box 3 Steps 1-7 ; then if I
change the customer account combo 1 to 3537 which has job
codes 1-7 and steps combo 3 for that... If I originally
had the job 22 selected from the first customer, the new
account 3537 doesn't have that job number and if I attempt
to save the record I get an error saying that the value I
am trying to set is invalid and I am asked to debug the
code. However if I do change the second and third combo I
can save the records correctly. I am posting all of the
code and I hope my explaination of the problem is
solvable....
Click to expand...

It seems to me we're dealing with a logic error here. What do you
*want* to have happen if you change the value of cmbCust_Acct such that
the values in the dependent combo boxes are no longer valid according to
those combo's lists? It seems to me that you would want to set them to
Null so as to force the user to select new, valid values. That sounds
like what you were doing before, except that you were doing it
unconditionally, rather than only when the existing values were no
longer in the list. You said that was a problem, though again it seems
perfectly reasonable to me. I'm pretty sure you could set it up so that
the dependent combos are only set to Null when their existing values are
no longer in the list, but aside from that I don't know what you might
want to do. Please clarify.
 
What I *want* to happen is if a user changes the
cmbCust_Acct and the values are not vaild on the other
list to set them to Null and force the user to enter new
items on those list.

The table where all of this data is stored as to be 100%
correct all of the time. The Department of Labor comes 3
to 4 times a year to audit our payroll system (which I am
trying to take out of Dbase IV and put in Access). So I
don't ever want data to be saved to the table that isn't
100% correct. My problem is that I have to make this so
that users of a low computer skill can use this and if
they make an error the system directs them to the excat
field that is incorrect. Hope this clarifies.
 
What I *want* to happen is if a user changes the
cmbCust_Acct and the values are not vaild on the other
list to set them to Null and force the user to enter new
items on those list.
Click to expand...

Why should there be any invalid records in the table in the first
place!? Navigating to a different record in the table, and finding
invalid data there, suggests that your table currently contains bad
data: would it not be better to first clean up the table, and second
establish Relational Integrity to prevent invalid data from being
entered in the first place?
 
Steven M. Britton said:
What I *want* to happen is if a user changes the
cmbCust_Acct and the values are not vaild on the other
list to set them to Null and force the user to enter new
items on those list.

The table where all of this data is stored as to be 100%
correct all of the time. The Department of Labor comes 3
to 4 times a year to audit our payroll system (which I am
trying to take out of Dbase IV and put in Access). So I
don't ever want data to be saved to the table that isn't
100% correct. My problem is that I have to make this so
that users of a low computer skill can use this and if
they make an error the system directs them to the excat
field that is incorrect. Hope this clarifies.
Click to expand...

Try code like this. It seems to work for me:

Private Sub cmbCust_Acct_AfterUpdate()

Me.cmbJob_No.Requery

If Not IsNull(Me.cmbJob_No) Then
If Me.cmbJob_No.ListIndex < 0 Then
Me.cmbJob_No = Null
End If
End If

Me.cmbStep_No.Requery

If Not IsNull(Me.cmbStep_No) Then
If Me.cmbStep_No.ListIndex < 0 Then
Me.cmbStep_No = Null
End If
End If

End Sub


Private Sub cmbJob_No_AfterUpdate()

Me.cmbStep_No.Requery

If Not IsNull(Me.cmbStep_No) Then
If Me.cmbStep_No.ListIndex < 0 Then
Me.cmbStep_No = Null
End If
End If

End Sub
 
I loaded your code and received an error when I changed
the cmbCust_Acct.
"The field 'PAYROLL.STEP_NO' cannot contain a Null value
because the Required property fo rthis field is set to
True. Enter a vaule in this field."

So I changed the to make the fields goto *Zero* and added
this in BeforeUpdate on the form.

If IsNull(Me.cmbStep_No) = True Or Me!cmbStep_No = "" Then
Cancel = True
strMsg = strMsg & "Step Number Required." & vbCrLf
strField = "cmbStep_No"
Else
If Me.cmbJob_No = 0 Or Me.cmbStep_No = 0 Then
MsgBox "Please Update your Job and Step to save
this record.", vbOKOnly, "Warning"
Me.cmbJob_No.SetFocus
Else
Me.JOB_CODE = Me.RATETABL_subform1.Form!JOB_CODE
Me.PIECERATE = Me.RATETABL_subform1.Form!PIECERATE
Me.PRVL_RATE = Me.RATETABL_subform1.Form!PRVL_RATE
End If
End If

This worked and sent me to the cmbJob_No, but now the
problem is that if I click save twice the first time I get
the msgbox and the second time is saves the zeros...

I am thinking of having a loop that keeps the user in
these combo whenever either of the fields are a *zero*,
but I don't know how to right the code. And again thank
you for you patience and help on this problem, if it
wasn't for this newsgroup I would go insane trying to
figure this out....

This is were I am and it's not working...

If IsNull(Me.cmbStep_No) = True Or Me!cmbStep_No = "" Then
Cancel = True
strMsg = strMsg & "Step Number Required." & vbCrLf
strField = "cmbStep_No"
Else
Do
If Me.cmbJob_No = 0 Or Me.cmbStep_No = 0 Then
MsgBox "Please Update your Job and Step to
save this record.", vbOKOnly, "Warning"
Me.cmbJob_No.SetFocus
Loop Until Me.cmbJob_No <> 0 Or Me.cmbStep_No <> 0
End If
Else
Me.JOB_CODE = Me.RATETABL_subform1.Form!JOB_CODE
Me.PIECERATE = Me.RATETABL_subform1.Form!PIECERATE
Me.PRVL_RATE = Me.RATETABL_subform1.Form!PRVL_RATE
End If
 
(comments interspersed with quoted text)

Steven M. Britton said:
I loaded your code and received an error when I changed
the cmbCust_Acct.
"The field 'PAYROLL.STEP_NO' cannot contain a Null value
because the Required property fo rthis field is set to
True. Enter a vaule in this field."
Click to expand...

Oops. I hadn't thought of that.
So I changed the to make the fields goto *Zero* [...]
Click to expand...

That should work.
[...] and added
this in BeforeUpdate on the form.

If IsNull(Me.cmbStep_No) = True Or Me!cmbStep_No = "" Then
Cancel = True
strMsg = strMsg & "Step Number Required." & vbCrLf
strField = "cmbStep_No"
Else
If Me.cmbJob_No = 0 Or Me.cmbStep_No = 0 Then
MsgBox "Please Update your Job and Step to save
this record.", vbOKOnly, "Warning"
Me.cmbJob_No.SetFocus
Else
Me.JOB_CODE = Me.RATETABL_subform1.Form!JOB_CODE
Me.PIECERATE = Me.RATETABL_subform1.Form!PIECERATE
Me.PRVL_RATE = Me.RATETABL_subform1.Form!PRVL_RATE
End If
End If

This worked and sent me to the cmbJob_No, but now the
problem is that if I click save twice the first time I get
the msgbox and the second time is saves the zeros...
Click to expand...

I think you left out a "Cancel = True" statement. But we can combine
some of your tests, because in this scheme a value of 0 for cmbStep_No
or cmbJob_No is the same as a missing value. How about logic like this
in your Form_BeforeUpdate procedure?

If Me.cmbStep_No & "0" = "0" Then
Cancel = True
strMsg = strMsg & "Step Number Required." & vbCrLf
If Len(strField) = 0 Then strField = "cmbStep_No"
End If

If Me.cmbJob_No & "0" = "0" Then
Cancel = True
strMsg = strMsg & "Job Number Required." & vbCrLf
If Len(strField) = 0 Then strField = "cmbJob_No"
End If
 
IT WORKS with some exceptions... This is the code I ended
up with.

If Me.cmbJob_No = "0" Or IsNull(Me.cmbJob_No) = True Or Me!
cmbJob_No = "" Then
Cancel = True
strMsg = strMsg & "Job Number Required." & vbCrLf
If Len(strField) = 0 Then strField = "cmbJob_No"
End If
If Me.cmbStep_No = "0" Or IsNull(Me.cmbStep_No) = True Or
Me!cmbStep_No = "" Then
Cancel = True
strMsg = strMsg & "Step Number Required." & vbCrLf
If Len(strField) = 0 Then strField = "cmbStep_No"

Thank you for all the help and with that said "One Last
Question" - On a slightly related, but different subject.

When I click the button to save this record and get my
strMsg after I click OK, I get the err.description from
the btnSave_Record. Is it possilbe to supress this error
when my required field error comes first? THANK YOU THANK
YOU.
-----Original Message-----
(comments interspersed with quoted text)

message news:[email protected]
I loaded your code and received an error when I changed
the cmbCust_Acct.
"The field 'PAYROLL.STEP_NO' cannot contain a Null value
because the Required property fo rthis field is set to
True. Enter a vaule in this field."
Click to expand...

Oops. I hadn't thought of that.
So I changed the to make the fields goto *Zero* [...]
Click to expand...

That should work.
[...] and added
this in BeforeUpdate on the form.

If IsNull(Me.cmbStep_No) = True Or Me!cmbStep_No = "" Then
Cancel = True
strMsg = strMsg & "Step Number Required." & vbCrLf
strField = "cmbStep_No"
Else
If Me.cmbJob_No = 0 Or Me.cmbStep_No = 0 Then
MsgBox "Please Update your Job and Step to save
this record.", vbOKOnly, "Warning"
Me.cmbJob_No.SetFocus
Else
Me.JOB_CODE = Me.RATETABL_subform1.Form!JOB_CODE
Me.PIECERATE = Me.RATETABL_subform1.Form! PIECERATE
Me.PRVL_RATE = Me.RATETABL_subform1.Form! PRVL_RATE
End If
End If

This worked and sent me to the cmbJob_No, but now the
problem is that if I click save twice the first time I get
the msgbox and the second time is saves the zeros...
Click to expand...

I think you left out a "Cancel = True" statement. But we can combine
some of your tests, because in this scheme a value of 0 for cmbStep_No
or cmbJob_No is the same as a missing value. How about logic like this
in your Form_BeforeUpdate procedure?

If Me.cmbStep_No & "0" = "0" Then
Cancel = True
strMsg = strMsg & "Step Number Required." & vbCrLf
If Len(strField) = 0 Then strField = "cmbStep_No"
End If

If Me.cmbJob_No & "0" = "0" Then
Cancel = True
strMsg = strMsg & "Job Number Required." & vbCrLf
If Len(strField) = 0 Then strField = "cmbJob_No"
End If


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)



.
Click to expand...
 
IT WORKS with some exceptions... This is the code I ended
up with.
Click to expand...

What exceptions, I wonder? What prompted you to change my code? I
thought it should have the same effect as what you've got, so I wonder
where I went wrong.
If Me.cmbJob_No = "0" Or IsNull(Me.cmbJob_No) = True Or Me!
cmbJob_No = "" Then
Cancel = True
strMsg = strMsg & "Job Number Required." & vbCrLf
If Len(strField) = 0 Then strField = "cmbJob_No"
End If
If Me.cmbStep_No = "0" Or IsNull(Me.cmbStep_No) = True Or
Me!cmbStep_No = "" Then
Cancel = True
strMsg = strMsg & "Step Number Required." & vbCrLf
If Len(strField) = 0 Then strField = "cmbStep_No"

Thank you for all the help and with that said "One Last
Question" - On a slightly related, but different subject.

When I click the button to save this record and get my
strMsg after I click OK, I get the err.description from
the btnSave_Record. Is it possilbe to supress this error
when my required field error comes first? THANK YOU THANK
YOU.
Click to expand...

Change these lines:
Err_btnSaveRecord_Click:
MsgBox Err.Description
Resume Exit_btnSaveRecord_Click
Click to expand...

to these:

Err_btnSaveRecord_Click:
If Err.Number <> 2501 Then
MsgBox Err.Description
End If
Resume Exit_btnSaveRecord_Click
 
I changed the code because it seemed to just ignore it, it
didn't cause the system to create the strMsg and I could
save the *Zeros* to the table... And additionally I also
needed it not to allow Null values so I added that part.
But overall it does excatly what I want and I couldn't
have done it with you. Thanks Again.
 
Steven M. Britton said:
I changed the code because it seemed to just ignore it, it
didn't cause the system to create the strMsg and I could
save the *Zeros* to the table... And additionally I also
needed it not to allow Null values so I added that part.
But overall it does excatly what I want and I couldn't
have done it with you. Thanks Again.
Click to expand...

The way I wrote it originally was *intended* not to allow Nulls, because
Null concatenated with "0" = "0". However, I see I made a stupid error,
and should have written the test like this:

If Val("0" & Me.cmbStep_No) = 0 Then

(assuming, that is, that these combo boxes are bound to numeric fields).
<sigh>

Well, at any rate, it's working now, and that's a good thing. You're
welcome.
 
Back
Top