Combo Selections

[Pam]

I have a combo box on main form that populates a subform with datasheet.
The db is linked to another db with fields JobAssignment and
MachineAssignment and each is supplied with technicians names. The
datasheet shows, for the same job, a job assigned tech and a machine
assigned tech. The code in the AfterUpdate event for the combo box is for
the job assigned tech. Now I need for it to also show the machine assigned
tech. Is this possible? Not sure how to put an "and" clause in the code
below.

Private Sub Combo8_AfterUpdate()
Dim strSQL

strSQL = "Select* From qJobList Where [JobAssignment] = '" & Me![Combo8]
& "'"
Me.RecordSource = strSQL
End Sub

As always, any help is greatly appreciated!
Thanks,
Pam

 

[Guest]

This isn't YOUR statement, but I am often helped by first using the query
grid, and then View SQL, copy paste, and then start replacing values with dbl
quotes, singles quotes, etc. Like this:

From Query Grid:
SELECT luOwners.OwnerID, luOwners.Owner
FROM luOwners
WHERE (((luOwners.OwnerID)=1) AND ((luOwners.Owner)="me"));

Entering dbl/single quotes, etc.
strSQL = "SELECT luOwners.OwnerID, luOwners.Owner" & _
" FROM luOwners" & _
" WHERE (((luOwners.OwnerID)=" & Val & ") AND ((luOwners.Owner)='" & strVal
& "'));"

 

[Pam]

I've tried copying the SQL statement as suggested, but it still doesn't
work. I need the list to populate if the tech is listed in JobAssigned OR
MachineAssigned. I've tried the code below (from SQL statement from list
box, as well as some other combinations and can't seem to get anything to
work.

Private Sub Combo8_AfterUpdate()
Dim strSQL

strSQL = "SELECT qJobList.JobAssignment, qJobList.MachineAssignment" & _
"FROM qJobList" & _
"WHERE(((qJobList.JobAssignment) = " & Forms!fSwitchboard!Combo8 & ")
AND ((qJobList.Completed) Is Null)) Or (((qJobList.MachineAssignment) = " &
Forms!fSwitchboard!Combo8 & ") And ((qJobList.Completed) Is Null))"

Any help is very much appreciated!
Thanks, Pam

This isn't YOUR statement, but I am often helped by first using the query
grid, and then View SQL, copy paste, and then start replacing values with
dbl
quotes, singles quotes, etc. Like this:

From Query Grid:
SELECT luOwners.OwnerID, luOwners.Owner
FROM luOwners
WHERE (((luOwners.OwnerID)=1) AND ((luOwners.Owner)="me"));

Entering dbl/single quotes, etc.
strSQL = "SELECT luOwners.OwnerID, luOwners.Owner" & _
" FROM luOwners" & _
" WHERE (((luOwners.OwnerID)=" & Val & ") AND ((luOwners.Owner)='" &
strVal
& "'));"

I have a combo box on main form that populates a subform with datasheet.
The db is linked to another db with fields JobAssignment and
MachineAssignment and each is supplied with technicians names. The
datasheet shows, for the same job, a job assigned tech and a machine
assigned tech. The code in the AfterUpdate event for the combo box is
for
the job assigned tech. Now I need for it to also show the machine
assigned
tech. Is this possible? Not sure how to put an "and" clause in the code
below.

Private Sub Combo8_AfterUpdate()
Dim strSQL

strSQL = "Select* From qJobList Where [JobAssignment] = '" &
Me![Combo8]
& "'"
Me.RecordSource = strSQL
End Sub

As always, any help is greatly appreciated!
Thanks,
Pam

 

[Guest]

Hopefully, this won't insult your intelligence, but does each combo box
return a string, or is the stored value something else like a LONG?
Private Sub Combo8_AfterUpdate()
Dim strSQL
strSQL = "SELECT qJobList.JobAssignment, qJobList.MachineAssignment" & _
"FROM qJobList" & _
"WHERE(((qJobList.JobAssignment) = " & Forms!fSwitchboard!Combo8 & ")
AND ((qJobList.Completed) Is Null)) Or (((qJobList.MachineAssignment) = " &
Forms!fSwitchboard!Combo8 & ") And ((qJobList.Completed) Is Null))"

Any help is very much appreciated!
Thanks, Pam

I've tried copying the SQL statement as suggested, but it still doesn't
work. I need the list to populate if the tech is listed in JobAssigned OR
MachineAssigned. I've tried the code below (from SQL statement from list
box, as well as some other combinations and can't seem to get anything to
work.

Private Sub Combo8_AfterUpdate()
Dim strSQL

strSQL = "SELECT qJobList.JobAssignment, qJobList.MachineAssignment" & _
"FROM qJobList" & _
"WHERE(((qJobList.JobAssignment) = " & Forms!fSwitchboard!Combo8 & ")
AND ((qJobList.Completed) Is Null)) Or (((qJobList.MachineAssignment) = " &
Forms!fSwitchboard!Combo8 & ") And ((qJobList.Completed) Is Null))"

Any help is very much appreciated!
Thanks, Pam

This isn't YOUR statement, but I am often helped by first using the query
grid, and then View SQL, copy paste, and then start replacing values with
dbl
quotes, singles quotes, etc. Like this:

From Query Grid:
SELECT luOwners.OwnerID, luOwners.Owner
FROM luOwners
WHERE (((luOwners.OwnerID)=1) AND ((luOwners.Owner)="me"));

Entering dbl/single quotes, etc.
strSQL = "SELECT luOwners.OwnerID, luOwners.Owner" & _
" FROM luOwners" & _
" WHERE (((luOwners.OwnerID)=" & Val & ") AND ((luOwners.Owner)='" &
strVal
& "'));"

I have a combo box on main form that populates a subform with datasheet.
The db is linked to another db with fields JobAssignment and
MachineAssignment and each is supplied with technicians names. The
datasheet shows, for the same job, a job assigned tech and a machine
assigned tech. The code in the AfterUpdate event for the combo box is
for
the job assigned tech. Now I need for it to also show the machine
assigned
tech. Is this possible? Not sure how to put an "and" clause in the code
below.

Private Sub Combo8_AfterUpdate()
Dim strSQL

strSQL = "Select* From qJobList Where [JobAssignment] = '" &
Me![Combo8]
& "'"
Me.RecordSource = strSQL
End Sub

As always, any help is greatly appreciated!
Thanks,
Pam

 

[Pam]

It doesn't insult my intelligence, I'm not too good with code, so I'm not
quite sure what you're asking. There are two combo boxes, each with the same
list of tech names, on a form in a linked db. We select a tech for job
assignment (repair) and a tech for machine assignment (machine work) for the
same job. On the form I'm working on I have combo8 to list the tech's. In
the subform, it pulls the jobs for each tech based on his name in combo8.
This finally works fine, but now I need it to show the job under the tech
that is job assigned and also under the tech for the machine assigned. I
can enter code to show either job or machine assigned, but not both.
Thanks for your help!
Pam

Hopefully, this won't insult your intelligence, but does each combo box
return a string, or is the stored value something else like a LONG?
Private Sub Combo8_AfterUpdate()
Dim strSQL
strSQL = "SELECT qJobList.JobAssignment, qJobList.MachineAssignment" &
_
"FROM qJobList" & _
"WHERE(((qJobList.JobAssignment) = " & Forms!fSwitchboard!Combo8 & ")
AND ((qJobList.Completed) Is Null)) Or (((qJobList.MachineAssignment) = "
&
Forms!fSwitchboard!Combo8 & ") And ((qJobList.Completed) Is Null))"

Any help is very much appreciated!
Thanks, Pam

I've tried copying the SQL statement as suggested, but it still doesn't
work. I need the list to populate if the tech is listed in JobAssigned
OR
MachineAssigned. I've tried the code below (from SQL statement from list
box, as well as some other combinations and can't seem to get anything to
work.

Private Sub Combo8_AfterUpdate()
Dim strSQL

strSQL = "SELECT qJobList.JobAssignment, qJobList.MachineAssignment"
& _
"FROM qJobList" & _
"WHERE(((qJobList.JobAssignment) = " & Forms!fSwitchboard!Combo8 & ")
AND ((qJobList.Completed) Is Null)) Or (((qJobList.MachineAssignment) = "
&
Forms!fSwitchboard!Combo8 & ") And ((qJobList.Completed) Is Null))"

Any help is very much appreciated!
Thanks, Pam

This isn't YOUR statement, but I am often helped by first using the
query
grid, and then View SQL, copy paste, and then start replacing values
with
dbl
quotes, singles quotes, etc. Like this:

From Query Grid:
SELECT luOwners.OwnerID, luOwners.Owner
FROM luOwners
WHERE (((luOwners.OwnerID)=1) AND ((luOwners.Owner)="me"));

Entering dbl/single quotes, etc.
strSQL = "SELECT luOwners.OwnerID, luOwners.Owner" & _
" FROM luOwners" & _
" WHERE (((luOwners.OwnerID)=" & Val & ") AND ((luOwners.Owner)='" &
strVal
& "'));"



:

I have a combo box on main form that populates a subform with
datasheet.
The db is linked to another db with fields JobAssignment and
MachineAssignment and each is supplied with technicians names. The
datasheet shows, for the same job, a job assigned tech and a machine
assigned tech. The code in the AfterUpdate event for the combo box is
for
the job assigned tech. Now I need for it to also show the machine
assigned
tech. Is this possible? Not sure how to put an "and" clause in the
code
below.

Private Sub Combo8_AfterUpdate()
Dim strSQL

strSQL = "Select* From qJobList Where [JobAssignment] = '" &
Me![Combo8]
& "'"
Me.RecordSource = strSQL
End Sub

As always, any help is greatly appreciated!
Thanks,
Pam

 

[Guest]

In design mode, when you double click on the combo box, and look on the data
tab, is the bound column "1"? And if so, what is the datatype of column "1".

Is it a string, a long integer, an integer, etc.

That is, when you created the original table, do you have a Primary key for
these records, is that primary key an auotnumber? If it is, it's a LONG.

Also, your latest examples did not have a space between the quote and the
FROM, or the quote and the WHERE ...

It doesn't insult my intelligence, I'm not too good with code, so I'm not
quite sure what you're asking. There are two combo boxes, each with the same
list of tech names, on a form in a linked db. We select a tech for job
assignment (repair) and a tech for machine assignment (machine work) for the
same job. On the form I'm working on I have combo8 to list the tech's. In
the subform, it pulls the jobs for each tech based on his name in combo8.
This finally works fine, but now I need it to show the job under the tech
that is job assigned and also under the tech for the machine assigned. I
can enter code to show either job or machine assigned, but not both.
Thanks for your help!
Pam

Hopefully, this won't insult your intelligence, but does each combo box
return a string, or is the stored value something else like a LONG?
Private Sub Combo8_AfterUpdate()
Dim strSQL
strSQL = "SELECT qJobList.JobAssignment, qJobList.MachineAssignment" &
_
"FROM qJobList" & _
"WHERE(((qJobList.JobAssignment) = " & Forms!fSwitchboard!Combo8 & ")
AND ((qJobList.Completed) Is Null)) Or (((qJobList.MachineAssignment) = "
&
Forms!fSwitchboard!Combo8 & ") And ((qJobList.Completed) Is Null))"

Any help is very much appreciated!
Thanks, Pam

I've tried copying the SQL statement as suggested, but it still doesn't
work. I need the list to populate if the tech is listed in JobAssigned
OR
MachineAssigned. I've tried the code below (from SQL statement from list
box, as well as some other combinations and can't seem to get anything to
work.

Private Sub Combo8_AfterUpdate()
Dim strSQL

strSQL = "SELECT qJobList.JobAssignment, qJobList.MachineAssignment"
& _
"FROM qJobList" & _
"WHERE(((qJobList.JobAssignment) = " & Forms!fSwitchboard!Combo8 & ")
AND ((qJobList.Completed) Is Null)) Or (((qJobList.MachineAssignment) = "
&
Forms!fSwitchboard!Combo8 & ") And ((qJobList.Completed) Is Null))"

Any help is very much appreciated!
Thanks, Pam





This isn't YOUR statement, but I am often helped by first using the
query
grid, and then View SQL, copy paste, and then start replacing values
with
dbl
quotes, singles quotes, etc. Like this:

From Query Grid:
SELECT luOwners.OwnerID, luOwners.Owner
FROM luOwners
WHERE (((luOwners.OwnerID)=1) AND ((luOwners.Owner)="me"));

Entering dbl/single quotes, etc.
strSQL = "SELECT luOwners.OwnerID, luOwners.Owner" & _
" FROM luOwners" & _
" WHERE (((luOwners.OwnerID)=" & Val & ") AND ((luOwners.Owner)='" &
strVal
& "'));"



:

I have a combo box on main form that populates a subform with
datasheet.
The db is linked to another db with fields JobAssignment and
MachineAssignment and each is supplied with technicians names. The
datasheet shows, for the same job, a job assigned tech and a machine
assigned tech. The code in the AfterUpdate event for the combo box is
for
the job assigned tech. Now I need for it to also show the machine
assigned
tech. Is this possible? Not sure how to put an "and" clause in the
code
below.

Private Sub Combo8_AfterUpdate()
Dim strSQL

strSQL = "Select* From qJobList Where [JobAssignment] = '" &
Me![Combo8]
& "'"
Me.RecordSource = strSQL
End Sub

As always, any help is greatly appreciated!
Thanks,
Pam

 

[Pam]

Yes, the combo box bound column is "1" and it is a text column - a tech's
name. The JobAssignment and MachineAssignment fields in the subform are
also text fields - the same tech names. This is really frustrating - I have
tried so many variations of code and nothing seems to work. I can get one
or the other but not both and I know there must be a way to do this. I
really appreciate all your help with this. Thanks, Pam

In design mode, when you double click on the combo box, and look on the
data
tab, is the bound column "1"? And if so, what is the datatype of column
"1".

Is it a string, a long integer, an integer, etc.

That is, when you created the original table, do you have a Primary key
for
these records, is that primary key an auotnumber? If it is, it's a LONG.

Also, your latest examples did not have a space between the quote and the
FROM, or the quote and the WHERE ...

It doesn't insult my intelligence, I'm not too good with code, so I'm not
quite sure what you're asking. There are two combo boxes, each with the
same
list of tech names, on a form in a linked db. We select a tech for job
assignment (repair) and a tech for machine assignment (machine work) for
the
same job. On the form I'm working on I have combo8 to list the tech's.
In
the subform, it pulls the jobs for each tech based on his name in combo8.
This finally works fine, but now I need it to show the job under the tech
that is job assigned and also under the tech for the machine assigned. I
can enter code to show either job or machine assigned, but not both.
Thanks for your help!
Pam

Hopefully, this won't insult your intelligence, but does each combo box
return a string, or is the stored value something else like a LONG?
Private Sub Combo8_AfterUpdate()
Dim strSQL
strSQL = "SELECT qJobList.JobAssignment, qJobList.MachineAssignment"
&
_
"FROM qJobList" & _
"WHERE(((qJobList.JobAssignment) = " & Forms!fSwitchboard!Combo8 &
")
AND ((qJobList.Completed) Is Null)) Or (((qJobList.MachineAssignment) =
"
&
Forms!fSwitchboard!Combo8 & ") And ((qJobList.Completed) Is Null))"

Any help is very much appreciated!
Thanks, Pam




:

I've tried copying the SQL statement as suggested, but it still
doesn't
work. I need the list to populate if the tech is listed in
JobAssigned
OR
MachineAssigned. I've tried the code below (from SQL statement from
list
box, as well as some other combinations and can't seem to get anything
to
work.

Private Sub Combo8_AfterUpdate()
Dim strSQL

strSQL = "SELECT qJobList.JobAssignment,
qJobList.MachineAssignment"
& _
"FROM qJobList" & _
"WHERE(((qJobList.JobAssignment) = " & Forms!fSwitchboard!Combo8 &
")
AND ((qJobList.Completed) Is Null)) Or (((qJobList.MachineAssignment)
= "
&
Forms!fSwitchboard!Combo8 & ") And ((qJobList.Completed) Is Null))"

Any help is very much appreciated!
Thanks, Pam





This isn't YOUR statement, but I am often helped by first using the
query
grid, and then View SQL, copy paste, and then start replacing values
with
dbl
quotes, singles quotes, etc. Like this:

From Query Grid:
SELECT luOwners.OwnerID, luOwners.Owner
FROM luOwners
WHERE (((luOwners.OwnerID)=1) AND ((luOwners.Owner)="me"));

Entering dbl/single quotes, etc.
strSQL = "SELECT luOwners.OwnerID, luOwners.Owner" & _
" FROM luOwners" & _
" WHERE (((luOwners.OwnerID)=" & Val & ") AND ((luOwners.Owner)='" &
strVal
& "'));"



:

I have a combo box on main form that populates a subform with
datasheet.
The db is linked to another db with fields JobAssignment and
MachineAssignment and each is supplied with technicians names. The
datasheet shows, for the same job, a job assigned tech and a
machine
assigned tech. The code in the AfterUpdate event for the combo box
is
for
the job assigned tech. Now I need for it to also show the machine
assigned
tech. Is this possible? Not sure how to put an "and" clause in
the
code
below.

Private Sub Combo8_AfterUpdate()
Dim strSQL

strSQL = "Select* From qJobList Where [JobAssignment] = '" &
Me![Combo8]
& "'"
Me.RecordSource = strSQL
End Sub

As always, any help is greatly appreciated!
Thanks,
Pam

 

[Pam]

I've tried a different variation of the code. Will you please look at this
and let me know what is wrong? I copied this from the SQL statement from
the subform. It will not populate the subform with a list of jobs for a
tech when tech is selected in combo8. I can remove the machine assigned
part of code and it will list the job assigned or vice versa - but not both.
Private Sub Combo8_AfterUpdate ()
Dim strSQL
strSQL = "SELECT qJobList.[JobAssignment], qJobList.[MachineAssignment],
qJobList.[PumpType], qJobList.[JobNumber], qJobList.[CustomerName],
qJobList.[ReceiptOfGoods], qJobList.[PriceQuote], qJobList.[ReadyToRepair],
qJobList.[Completed], qJobList.[HotJob], qJobList.[MachineStart],
qJobList.[MachineFinish]" & _
"FROM qJobList " & _
"WHERE qJobList.[JobAssignment] = '" & Me.Combo8.Value & "'" & _
"Or qJobList.[MachineAssignment] = '" & Me.Combo8.Value & "';"

End Sub

In design mode, when you double click on the combo box, and look on the
data
tab, is the bound column "1"? And if so, what is the datatype of column
"1".

Is it a string, a long integer, an integer, etc.

That is, when you created the original table, do you have a Primary key
for
these records, is that primary key an auotnumber? If it is, it's a LONG.

Also, your latest examples did not have a space between the quote and the
FROM, or the quote and the WHERE ...

It doesn't insult my intelligence, I'm not too good with code, so I'm not
quite sure what you're asking. There are two combo boxes, each with the
same
list of tech names, on a form in a linked db. We select a tech for job
assignment (repair) and a tech for machine assignment (machine work) for
the
same job. On the form I'm working on I have combo8 to list the tech's.
In
the subform, it pulls the jobs for each tech based on his name in combo8.
This finally works fine, but now I need it to show the job under the tech
that is job assigned and also under the tech for the machine assigned. I
can enter code to show either job or machine assigned, but not both.
Thanks for your help!
Pam

Hopefully, this won't insult your intelligence, but does each combo box
return a string, or is the stored value something else like a LONG?
Private Sub Combo8_AfterUpdate()
Dim strSQL
strSQL = "SELECT qJobList.JobAssignment, qJobList.MachineAssignment"
&
_
"FROM qJobList" & _
"WHERE(((qJobList.JobAssignment) = " & Forms!fSwitchboard!Combo8 &
")
AND ((qJobList.Completed) Is Null)) Or (((qJobList.MachineAssignment) =
"
&
Forms!fSwitchboard!Combo8 & ") And ((qJobList.Completed) Is Null))"

Any help is very much appreciated!
Thanks, Pam




:

I've tried copying the SQL statement as suggested, but it still
doesn't
work. I need the list to populate if the tech is listed in
JobAssigned
OR
MachineAssigned. I've tried the code below (from SQL statement from
list
box, as well as some other combinations and can't seem to get anything
to
work.

Private Sub Combo8_AfterUpdate()
Dim strSQL

strSQL = "SELECT qJobList.JobAssignment,
qJobList.MachineAssignment"
& _
"FROM qJobList" & _
"WHERE(((qJobList.JobAssignment) = " & Forms!fSwitchboard!Combo8 &
")
AND ((qJobList.Completed) Is Null)) Or (((qJobList.MachineAssignment)
= "
&
Forms!fSwitchboard!Combo8 & ") And ((qJobList.Completed) Is Null))"

Any help is very much appreciated!
Thanks, Pam





This isn't YOUR statement, but I am often helped by first using the
query
grid, and then View SQL, copy paste, and then start replacing values
with
dbl
quotes, singles quotes, etc. Like this:

From Query Grid:
SELECT luOwners.OwnerID, luOwners.Owner
FROM luOwners
WHERE (((luOwners.OwnerID)=1) AND ((luOwners.Owner)="me"));

Entering dbl/single quotes, etc.
strSQL = "SELECT luOwners.OwnerID, luOwners.Owner" & _
" FROM luOwners" & _
" WHERE (((luOwners.OwnerID)=" & Val & ") AND ((luOwners.Owner)='" &
strVal
& "'));"



:

I have a combo box on main form that populates a subform with
datasheet.
The db is linked to another db with fields JobAssignment and
MachineAssignment and each is supplied with technicians names. The
datasheet shows, for the same job, a job assigned tech and a
machine
assigned tech. The code in the AfterUpdate event for the combo box
is
for
the job assigned tech. Now I need for it to also show the machine
assigned
tech. Is this possible? Not sure how to put an "and" clause in
the
code
below.

Private Sub Combo8_AfterUpdate()
Dim strSQL

strSQL = "Select* From qJobList Where [JobAssignment] = '" &
Me![Combo8]
& "'"
Me.RecordSource = strSQL
End Sub

As always, any help is greatly appreciated!
Thanks,
Pam