Creating a query using multiselect list box and text boxes

  • Thread starter Thread starter Rob
  • Start date Start date
R

Rob

I would like to create a query that would update a table based on the data
entered in two text boxes. The criteria for updating this table would be
based on a multiselect list box... So basically the query reads as such:


UPDATE tblQualChecklist SET tblQualChecklist.Sequence =
[Forms]![frmUpdateCheklist]![Sequence], tblQualChecklist.Activity =
[Forms]![frmUpdateCheklist]![Activity]
WHERE (((tblQualChecklist.DocType)=[Forms]![frmUpdateCheklist]![lstDocType]));


The "Where" clause is the list box. As of now I only get one record
updated. How do I get the query to update based on more than one selection
in the multi select list box.


Also, I would like to create a check box that would select all the items in
the multislect list box and update accordingly. I was thinking that I can
use the "Or is Null" extension on the "Where" clause in the query but I
believe my user's would prefer clicking a check box that would highlight all
the items in the list box and the query based on those selections.

Please Help!!
 
Here is a handy function to do exactly that. It is set up so that if no
selections are made, then all are included. If you want the user to have to
select all, you would need some code behing the command button. See Below:

Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select

BuildWhereCondition = strWhere

End Function

Public Sub SelectAll()
Dim ctl As Control
Dim lngX As Long

Set ctl = Forms!MyForm!MyListBox
For lngX = 0 To ctl.ListCount - 1
ctl.Selected(lngX) = True
Next lngX
Set ctl = Nothing
End Sub
 
Thanks so much for the quick response but I don't quite understand. Where do
I specify to update the table based on the data in the "Activity field" and
the"Sequence" field. And where do I mention my list box for looking up the
criteria.

Klatuu said:
Here is a handy function to do exactly that. It is set up so that if no
selections are made, then all are included. If you want the user to have to
select all, you would need some code behing the command button. See Below:

Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select

BuildWhereCondition = strWhere

End Function

Public Sub SelectAll()
Dim ctl As Control
Dim lngX As Long

Set ctl = Forms!MyForm!MyListBox
For lngX = 0 To ctl.ListCount - 1
ctl.Selected(lngX) = True
Next lngX
Set ctl = Nothing
End Sub

--
Dave Hargis, Microsoft Access MVP


Rob said:
I would like to create a query that would update a table based on the data
entered in two text boxes. The criteria for updating this table would be
based on a multiselect list box... So basically the query reads as such:


UPDATE tblQualChecklist SET tblQualChecklist.Sequence =
[Forms]![frmUpdateCheklist]![Sequence], tblQualChecklist.Activity =
[Forms]![frmUpdateCheklist]![Activity]
WHERE (((tblQualChecklist.DocType)=[Forms]![frmUpdateCheklist]![lstDocType]));


The "Where" clause is the list box. As of now I only get one record
updated. How do I get the query to update based on more than one selection
in the multi select list box.


Also, I would like to create a check box that would select all the items in
the multislect list box and update accordingly. I was thinking that I can
use the "Or is Null" extension on the "Where" clause in the query but I
believe my user's would prefer clicking a check box that would highlight all
the items in the list box and the query based on those selections.

Please Help!!
Click to expand...
Click to expand...
 
By calling the BuildWhereCondition function and passing the control to it,
the return is a string with all the selections in it. You just need to add
the word Where in front of it. Then you use the where condition string in
your query to do the updates.
--
Dave Hargis, Microsoft Access MVP


Rob said:
Thanks so much for the quick response but I don't quite understand. Where do
I specify to update the table based on the data in the "Activity field" and
the"Sequence" field. And where do I mention my list box for looking up the
criteria.

Klatuu said:
Here is a handy function to do exactly that. It is set up so that if no
selections are made, then all are included. If you want the user to have to
select all, you would need some code behing the command button. See Below:

Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select

BuildWhereCondition = strWhere

End Function

Public Sub SelectAll()
Dim ctl As Control
Dim lngX As Long

Set ctl = Forms!MyForm!MyListBox
For lngX = 0 To ctl.ListCount - 1
ctl.Selected(lngX) = True
Next lngX
Set ctl = Nothing
End Sub

--
Dave Hargis, Microsoft Access MVP


Rob said:
I would like to create a query that would update a table based on the data
entered in two text boxes. The criteria for updating this table would be
based on a multiselect list box... So basically the query reads as such:


UPDATE tblQualChecklist SET tblQualChecklist.Sequence =
[Forms]![frmUpdateCheklist]![Sequence], tblQualChecklist.Activity =
[Forms]![frmUpdateCheklist]![Activity]
WHERE (((tblQualChecklist.DocType)=[Forms]![frmUpdateCheklist]![lstDocType]));


The "Where" clause is the list box. As of now I only get one record
updated. How do I get the query to update based on more than one selection
in the multi select list box.


Also, I would like to create a check box that would select all the items in
the multislect list box and update accordingly. I was thinking that I can
use the "Or is Null" extension on the "Where" clause in the query but I
believe my user's would prefer clicking a check box that would highlight all
the items in the list box and the query based on those selections.

Please Help!!
Click to expand...
Click to expand...
Click to expand...
 
Ok, I get how to call the function in the query, but I get an error message
saying invalid use of Me. at the " Set ctl = Me.Controls(strControl)".

The name of the list box control is "lstDocType". I am stumped again.


Klatuu said:
By calling the BuildWhereCondition function and passing the control to it,
the return is a string with all the selections in it. You just need to add
the word Where in front of it. Then you use the where condition string in
your query to do the updates.
--
Dave Hargis, Microsoft Access MVP


Rob said:
Thanks so much for the quick response but I don't quite understand. Where do
I specify to update the table based on the data in the "Activity field" and
the"Sequence" field. And where do I mention my list box for looking up the
criteria.

Klatuu said:
Here is a handy function to do exactly that. It is set up so that if no
selections are made, then all are included. If you want the user to have to
select all, you would need some code behing the command button. See Below:

Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select

BuildWhereCondition = strWhere

End Function

Public Sub SelectAll()
Dim ctl As Control
Dim lngX As Long

Set ctl = Forms!MyForm!MyListBox
For lngX = 0 To ctl.ListCount - 1
ctl.Selected(lngX) = True
Next lngX
Set ctl = Nothing
End Sub

--
Dave Hargis, Microsoft Access MVP


:

I would like to create a query that would update a table based on the data
entered in two text boxes. The criteria for updating this table would be
based on a multiselect list box... So basically the query reads as such:


UPDATE tblQualChecklist SET tblQualChecklist.Sequence =
[Forms]![frmUpdateCheklist]![Sequence], tblQualChecklist.Activity =
[Forms]![frmUpdateCheklist]![Activity]
WHERE (((tblQualChecklist.DocType)=[Forms]![frmUpdateCheklist]![lstDocType]));


The "Where" clause is the list box. As of now I only get one record
updated. How do I get the query to update based on more than one selection
in the multi select list box.


Also, I would like to create a check box that would select all the items in
the multislect list box and update accordingly. I was thinking that I can
use the "Or is Null" extension on the "Where" clause in the query but I
believe my user's would prefer clicking a check box that would highlight all
the items in the list box and the query based on those selections.

Please Help!!
Click to expand...
Click to expand...
Click to expand...
 
I thought I had figured out what to use instead of ME. I used this "set ctl
= Forms!frmnewactivity.lstDocType. This seems to work and run through the
function fine but when I get to the "End Function" it says that this can't be
used at this time. I tried to change the funciton to a private function
instead of public and it says that this function has not been defined.
Almost there. Please help!

Klatuu said:
By calling the BuildWhereCondition function and passing the control to it,
the return is a string with all the selections in it. You just need to add
the word Where in front of it. Then you use the where condition string in
your query to do the updates.
--
Dave Hargis, Microsoft Access MVP


Rob said:
Thanks so much for the quick response but I don't quite understand. Where do
I specify to update the table based on the data in the "Activity field" and
the"Sequence" field. And where do I mention my list box for looking up the
criteria.

Klatuu said:
Here is a handy function to do exactly that. It is set up so that if no
selections are made, then all are included. If you want the user to have to
select all, you would need some code behing the command button. See Below:

Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select

BuildWhereCondition = strWhere

End Function

Public Sub SelectAll()
Dim ctl As Control
Dim lngX As Long

Set ctl = Forms!MyForm!MyListBox
For lngX = 0 To ctl.ListCount - 1
ctl.Selected(lngX) = True
Next lngX
Set ctl = Nothing
End Sub

--
Dave Hargis, Microsoft Access MVP


:

I would like to create a query that would update a table based on the data
entered in two text boxes. The criteria for updating this table would be
based on a multiselect list box... So basically the query reads as such:


UPDATE tblQualChecklist SET tblQualChecklist.Sequence =
[Forms]![frmUpdateCheklist]![Sequence], tblQualChecklist.Activity =
[Forms]![frmUpdateCheklist]![Activity]
WHERE (((tblQualChecklist.DocType)=[Forms]![frmUpdateCheklist]![lstDocType]));


The "Where" clause is the list box. As of now I only get one record
updated. How do I get the query to update based on more than one selection
in the multi select list box.


Also, I would like to create a check box that would select all the items in
the multislect list box and update accordingly. I was thinking that I can
use the "Or is Null" extension on the "Where" clause in the query but I
believe my user's would prefer clicking a check box that would highlight all
the items in the list box and the query based on those selections.

Please Help!!
Click to expand...
Click to expand...
Click to expand...
 
I don't understand the problem. The Me part you figured out. But, since you
don't have the code in the form module, it does need to be public or it can't
be seen outside the module it is in.

Can you post the error number and exact text of the error? It doesn't seem
to make sense to me.
--
Dave Hargis, Microsoft Access MVP


Rob said:
I thought I had figured out what to use instead of ME. I used this "set ctl
= Forms!frmnewactivity.lstDocType. This seems to work and run through the
function fine but when I get to the "End Function" it says that this can't be
used at this time. I tried to change the funciton to a private function
instead of public and it says that this function has not been defined.
Almost there. Please help!

Klatuu said:
By calling the BuildWhereCondition function and passing the control to it,
the return is a string with all the selections in it. You just need to add
the word Where in front of it. Then you use the where condition string in
your query to do the updates.
--
Dave Hargis, Microsoft Access MVP


Rob said:
Thanks so much for the quick response but I don't quite understand. Where do
I specify to update the table based on the data in the "Activity field" and
the"Sequence" field. And where do I mention my list box for looking up the
criteria.

:

Here is a handy function to do exactly that. It is set up so that if no
selections are made, then all are included. If you want the user to have to
select all, you would need some code behing the command button. See Below:

Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select

BuildWhereCondition = strWhere

End Function

Public Sub SelectAll()
Dim ctl As Control
Dim lngX As Long

Set ctl = Forms!MyForm!MyListBox
For lngX = 0 To ctl.ListCount - 1
ctl.Selected(lngX) = True
Next lngX
Set ctl = Nothing
End Sub

--
Dave Hargis, Microsoft Access MVP


:

I would like to create a query that would update a table based on the data
entered in two text boxes. The criteria for updating this table would be
based on a multiselect list box... So basically the query reads as such:


UPDATE tblQualChecklist SET tblQualChecklist.Sequence =
[Forms]![frmUpdateCheklist]![Sequence], tblQualChecklist.Activity =
[Forms]![frmUpdateCheklist]![Activity]
WHERE (((tblQualChecklist.DocType)=[Forms]![frmUpdateCheklist]![lstDocType]));


The "Where" clause is the list box. As of now I only get one record
updated. How do I get the query to update based on more than one selection
in the multi select list box.


Also, I would like to create a check box that would select all the items in
the multislect list box and update accordingly. I was thinking that I can
use the "Or is Null" extension on the "Where" clause in the query but I
believe my user's would prefer clicking a check box that would highlight all
the items in the list box and the query based on those selections.

Please Help!!
Click to expand...
Click to expand...
Click to expand...
 
Sequence Activity DocType
42 Verify PDF (composite and recent revision) TR/ADDENDUM
63 Verify Work Request Scope TR/ADDENDUM
201 Verify applicable Team Review TR/ADDENDUM
202 Verify Engineer Review TR/ADDENDUM
203 Verify that kickoff notes TR/ADDENDUM
204 Verify that PDFs TR/ADDENDUM

Here is an example of the fields. There are 32 different doc types showing
in my list box. I want to be able to append to this table a new Sequence #
that corresponds to a new Activity for certain Doc Types.

The SQL and funcrtion run completed through, but I get no records appended
to the table.

You have helped me with other problems in the past and I am learning so much
by working with Access and through you and others in this forum. Thanks
Again!!!

Here is the SQL:

INSERT INTO tblQualChecklist ( Activity, Sequence, DocType )
SELECT Forms!frmnewactivity!Activity AS Activity,
Forms!frmnewactivity!Sequence AS Sequence, DocType
FROM tblQualChecklist
WHERE (((DocType)=Finish_Click()));


Klatuu said:
I don't understand the problem. The Me part you figured out. But, since you
don't have the code in the form module, it does need to be public or it can't
be seen outside the module it is in.

Can you post the error number and exact text of the error? It doesn't seem
to make sense to me.
--
Dave Hargis, Microsoft Access MVP


Rob said:
I thought I had figured out what to use instead of ME. I used this "set ctl
= Forms!frmnewactivity.lstDocType. This seems to work and run through the
function fine but when I get to the "End Function" it says that this can't be
used at this time. I tried to change the funciton to a private function
instead of public and it says that this function has not been defined.
Almost there. Please help!

Klatuu said:
By calling the BuildWhereCondition function and passing the control to it,
the return is a string with all the selections in it. You just need to add
the word Where in front of it. Then you use the where condition string in
your query to do the updates.
--
Dave Hargis, Microsoft Access MVP


:

Thanks so much for the quick response but I don't quite understand. Where do
I specify to update the table based on the data in the "Activity field" and
the"Sequence" field. And where do I mention my list box for looking up the
criteria.

:

Here is a handy function to do exactly that. It is set up so that if no
selections are made, then all are included. If you want the user to have to
select all, you would need some code behing the command button. See Below:

Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select

BuildWhereCondition = strWhere

End Function

Public Sub SelectAll()
Dim ctl As Control
Dim lngX As Long

Set ctl = Forms!MyForm!MyListBox
For lngX = 0 To ctl.ListCount - 1
ctl.Selected(lngX) = True
Next lngX
Set ctl = Nothing
End Sub

--
Dave Hargis, Microsoft Access MVP


:

I would like to create a query that would update a table based on the data
entered in two text boxes. The criteria for updating this table would be
based on a multiselect list box... So basically the query reads as such:


UPDATE tblQualChecklist SET tblQualChecklist.Sequence =
[Forms]![frmUpdateCheklist]![Sequence], tblQualChecklist.Activity =
[Forms]![frmUpdateCheklist]![Activity]
WHERE (((tblQualChecklist.DocType)=[Forms]![frmUpdateCheklist]![lstDocType]));


The "Where" clause is the list box. As of now I only get one record
updated. How do I get the query to update based on more than one selection
in the multi select list box.


Also, I would like to create a check box that would select all the items in
the multislect list box and update accordingly. I was thinking that I can
use the "Or is Null" extension on the "Where" clause in the query but I
believe my user's would prefer clicking a check box that would highlight all
the items in the list box and the query based on those selections.

Please Help!!
Click to expand...
Click to expand...
Click to expand...
 
What is Finish_Click()?

I don't think that is going to work.
--
Dave Hargis, Microsoft Access MVP


Rob said:
Sequence Activity DocType
42 Verify PDF (composite and recent revision) TR/ADDENDUM
63 Verify Work Request Scope TR/ADDENDUM
201 Verify applicable Team Review TR/ADDENDUM
202 Verify Engineer Review TR/ADDENDUM
203 Verify that kickoff notes TR/ADDENDUM
204 Verify that PDFs TR/ADDENDUM

Here is an example of the fields. There are 32 different doc types showing
in my list box. I want to be able to append to this table a new Sequence #
that corresponds to a new Activity for certain Doc Types.

The SQL and funcrtion run completed through, but I get no records appended
to the table.

You have helped me with other problems in the past and I am learning so much
by working with Access and through you and others in this forum. Thanks
Again!!!

Here is the SQL:

INSERT INTO tblQualChecklist ( Activity, Sequence, DocType )
SELECT Forms!frmnewactivity!Activity AS Activity,
Forms!frmnewactivity!Sequence AS Sequence, DocType
FROM tblQualChecklist
WHERE (((DocType)=Finish_Click()));


Klatuu said:
I don't understand the problem. The Me part you figured out. But, since you
don't have the code in the form module, it does need to be public or it can't
be seen outside the module it is in.

Can you post the error number and exact text of the error? It doesn't seem
to make sense to me.
--
Dave Hargis, Microsoft Access MVP


Rob said:
I thought I had figured out what to use instead of ME. I used this "set ctl
= Forms!frmnewactivity.lstDocType. This seems to work and run through the
function fine but when I get to the "End Function" it says that this can't be
used at this time. I tried to change the funciton to a private function
instead of public and it says that this function has not been defined.
Almost there. Please help!

:

By calling the BuildWhereCondition function and passing the control to it,
the return is a string with all the selections in it. You just need to add
the word Where in front of it. Then you use the where condition string in
your query to do the updates.
--
Dave Hargis, Microsoft Access MVP


:

Thanks so much for the quick response but I don't quite understand. Where do
I specify to update the table based on the data in the "Activity field" and
the"Sequence" field. And where do I mention my list box for looking up the
criteria.

:

Here is a handy function to do exactly that. It is set up so that if no
selections are made, then all are included. If you want the user to have to
select all, you would need some code behing the command button. See Below:

Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select

BuildWhereCondition = strWhere

End Function

Public Sub SelectAll()
Dim ctl As Control
Dim lngX As Long

Set ctl = Forms!MyForm!MyListBox
For lngX = 0 To ctl.ListCount - 1
ctl.Selected(lngX) = True
Next lngX
Set ctl = Nothing
End Sub

--
Dave Hargis, Microsoft Access MVP


:

I would like to create a query that would update a table based on the data
entered in two text boxes. The criteria for updating this table would be
based on a multiselect list box... So basically the query reads as such:


UPDATE tblQualChecklist SET tblQualChecklist.Sequence =
[Forms]![frmUpdateCheklist]![Sequence], tblQualChecklist.Activity =
[Forms]![frmUpdateCheklist]![Activity]
WHERE (((tblQualChecklist.DocType)=[Forms]![frmUpdateCheklist]![lstDocType]));


The "Where" clause is the list box. As of now I only get one record
updated. How do I get the query to update based on more than one selection
in the multi select list box.


Also, I would like to create a check box that would select all the items in
the multislect list box and update accordingly. I was thinking that I can
use the "Or is Null" extension on the "Where" clause in the query but I
believe my user's would prefer clicking a check box that would highlight all
the items in the list box and the query based on those selections.

Please Help!!
Click to expand...
Click to expand...
Click to expand...
 
Finish_Click is the name of the public function. It is calling and and i
have set it so it "walks" through the code and it works but I am getting no
data.

Klatuu said:
What is Finish_Click()?

I don't think that is going to work.
--
Dave Hargis, Microsoft Access MVP


Rob said:
Sequence Activity DocType
42 Verify PDF (composite and recent revision) TR/ADDENDUM
63 Verify Work Request Scope TR/ADDENDUM
201 Verify applicable Team Review TR/ADDENDUM
202 Verify Engineer Review TR/ADDENDUM
203 Verify that kickoff notes TR/ADDENDUM
204 Verify that PDFs TR/ADDENDUM

Here is an example of the fields. There are 32 different doc types showing
in my list box. I want to be able to append to this table a new Sequence #
that corresponds to a new Activity for certain Doc Types.

The SQL and funcrtion run completed through, but I get no records appended
to the table.

You have helped me with other problems in the past and I am learning so much
by working with Access and through you and others in this forum. Thanks
Again!!!

Here is the SQL:

INSERT INTO tblQualChecklist ( Activity, Sequence, DocType )
SELECT Forms!frmnewactivity!Activity AS Activity,
Forms!frmnewactivity!Sequence AS Sequence, DocType
FROM tblQualChecklist
WHERE (((DocType)=Finish_Click()));


Klatuu said:
I don't understand the problem. The Me part you figured out. But, since you
don't have the code in the form module, it does need to be public or it can't
be seen outside the module it is in.

Can you post the error number and exact text of the error? It doesn't seem
to make sense to me.
--
Dave Hargis, Microsoft Access MVP


:

I thought I had figured out what to use instead of ME. I used this "set ctl
= Forms!frmnewactivity.lstDocType. This seems to work and run through the
function fine but when I get to the "End Function" it says that this can't be
used at this time. I tried to change the funciton to a private function
instead of public and it says that this function has not been defined.
Almost there. Please help!

:

By calling the BuildWhereCondition function and passing the control to it,
the return is a string with all the selections in it. You just need to add
the word Where in front of it. Then you use the where condition string in
your query to do the updates.
--
Dave Hargis, Microsoft Access MVP


:

Thanks so much for the quick response but I don't quite understand. Where do
I specify to update the table based on the data in the "Activity field" and
the"Sequence" field. And where do I mention my list box for looking up the
criteria.

:

Here is a handy function to do exactly that. It is set up so that if no
selections are made, then all are included. If you want the user to have to
select all, you would need some code behing the command button. See Below:

Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select

BuildWhereCondition = strWhere

End Function

Public Sub SelectAll()
Dim ctl As Control
Dim lngX As Long

Set ctl = Forms!MyForm!MyListBox
For lngX = 0 To ctl.ListCount - 1
ctl.Selected(lngX) = True
Next lngX
Set ctl = Nothing
End Sub

--
Dave Hargis, Microsoft Access MVP


:

I would like to create a query that would update a table based on the data
entered in two text boxes. The criteria for updating this table would be
based on a multiselect list box... So basically the query reads as such:


UPDATE tblQualChecklist SET tblQualChecklist.Sequence =
[Forms]![frmUpdateCheklist]![Sequence], tblQualChecklist.Activity =
[Forms]![frmUpdateCheklist]![Activity]
WHERE (((tblQualChecklist.DocType)=[Forms]![frmUpdateCheklist]![lstDocType]));


The "Where" clause is the list box. As of now I only get one record
updated. How do I get the query to update based on more than one selection
in the multi select list box.


Also, I would like to create a check box that would select all the items in
the multislect list box and update accordingly. I was thinking that I can
use the "Or is Null" extension on the "Where" clause in the query but I
believe my user's would prefer clicking a check box that would highlight all
the items in the list box and the query based on those selections.

Please Help!!
Click to expand...
Click to expand...
Click to expand...
 
Try changing the way you are doing it. Rather than use a stored query, build
the SQL statment in VBA. Put the results of the Finish_Click function into a
string variable and add it to the SQL. Then use the execute statment to run
the query:

Dim strSQL As String
Dim strWhere As String

strSQL = "INSERT INTO tblQualChecklist ( Activity, Sequence, DocType ) "
& _
"SELECT " & Forms!frmnewactivity!Activity & " AS Activity, " & _
Forms!frmnewactivity!Sequence & " AS Sequence, DocType " & _
"FROM tblQualChecklist WHERE DocType = "

strWhere = BuildWhereCondition(Me.MyListBoxName.Name)

strSQL = strSQL & strWhere

Docmd.Execute(strSQL), dbFailOnError
--
Dave Hargis, Microsoft Access MVP


Rob said:
Finish_Click is the name of the public function. It is calling and and i
have set it so it "walks" through the code and it works but I am getting no
data.

Klatuu said:
What is Finish_Click()?

I don't think that is going to work.
--
Dave Hargis, Microsoft Access MVP


Rob said:
Sequence Activity DocType
42 Verify PDF (composite and recent revision) TR/ADDENDUM
63 Verify Work Request Scope TR/ADDENDUM
201 Verify applicable Team Review TR/ADDENDUM
202 Verify Engineer Review TR/ADDENDUM
203 Verify that kickoff notes TR/ADDENDUM
204 Verify that PDFs TR/ADDENDUM

Here is an example of the fields. There are 32 different doc types showing
in my list box. I want to be able to append to this table a new Sequence #
that corresponds to a new Activity for certain Doc Types.

The SQL and funcrtion run completed through, but I get no records appended
to the table.

You have helped me with other problems in the past and I am learning so much
by working with Access and through you and others in this forum. Thanks
Again!!!

Here is the SQL:

INSERT INTO tblQualChecklist ( Activity, Sequence, DocType )
SELECT Forms!frmnewactivity!Activity AS Activity,
Forms!frmnewactivity!Sequence AS Sequence, DocType
FROM tblQualChecklist
WHERE (((DocType)=Finish_Click()));


:

I don't understand the problem. The Me part you figured out. But, since you
don't have the code in the form module, it does need to be public or it can't
be seen outside the module it is in.

Can you post the error number and exact text of the error? It doesn't seem
to make sense to me.
--
Dave Hargis, Microsoft Access MVP


:

I thought I had figured out what to use instead of ME. I used this "set ctl
= Forms!frmnewactivity.lstDocType. This seems to work and run through the
function fine but when I get to the "End Function" it says that this can't be
used at this time. I tried to change the funciton to a private function
instead of public and it says that this function has not been defined.
Almost there. Please help!

:

By calling the BuildWhereCondition function and passing the control to it,
the return is a string with all the selections in it. You just need to add
the word Where in front of it. Then you use the where condition string in
your query to do the updates.
--
Dave Hargis, Microsoft Access MVP


:

Thanks so much for the quick response but I don't quite understand. Where do
I specify to update the table based on the data in the "Activity field" and
the"Sequence" field. And where do I mention my list box for looking up the
criteria.

:

Here is a handy function to do exactly that. It is set up so that if no
selections are made, then all are included. If you want the user to have to
select all, you would need some code behing the command button. See Below:

Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select

BuildWhereCondition = strWhere

End Function

Public Sub SelectAll()
Dim ctl As Control
Dim lngX As Long

Set ctl = Forms!MyForm!MyListBox
For lngX = 0 To ctl.ListCount - 1
ctl.Selected(lngX) = True
Next lngX
Set ctl = Nothing
End Sub

--
Dave Hargis, Microsoft Access MVP


:

I would like to create a query that would update a table based on the data
entered in two text boxes. The criteria for updating this table would be
based on a multiselect list box... So basically the query reads as such:


UPDATE tblQualChecklist SET tblQualChecklist.Sequence =
[Forms]![frmUpdateCheklist]![Sequence], tblQualChecklist.Activity =
[Forms]![frmUpdateCheklist]![Activity]
WHERE (((tblQualChecklist.DocType)=[Forms]![frmUpdateCheklist]![lstDocType]));


The "Where" clause is the list box. As of now I only get one record
updated. How do I get the query to update based on more than one selection
in the multi select list box.


Also, I would like to create a check box that would select all the items in
the multislect list box and update accordingly. I was thinking that I can
use the "Or is Null" extension on the "Where" clause in the query but I
believe my user's would prefer clicking a check box that would highlight all
the items in the list box and the query based on those selections.

Please Help!!
Click to expand...
Click to expand...
Click to expand...
 
I hope you don't mind me bothering you. This almost works but it doesn't
seem to like the "Docmd.Execute" I changed the name of the function to
BuildWhereCondition and have changed the Me. to
Forms!frmnewactivity.lstDocType.Name)

So here is what they look like:

Private Sub Finish_Click()

Dim strSQL As String
Dim strWhere As String

strSQL = "INSERT INTO tblQualChecklist ( Activity, Sequence, DocType ) "
& _
"SELECT " & Forms!frmnewactivity!Activity & " AS Activity, " & _
Forms!frmnewactivity!Sequence & " AS Sequence, DocType " & _
"FROM tblQualChecklist WHERE DocType = "

strWhere = BuildWhereCondition(Me.lstDocType.Name)

strSQL = strSQL & strWhere

DoCmd.Execute strSQL), dbFailOnError

Public Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select

BuildWhereCondition = strWhere

End Function





End Sub



Klatuu said:
Try changing the way you are doing it. Rather than use a stored query, build
the SQL statment in VBA. Put the results of the Finish_Click function into a
string variable and add it to the SQL. Then use the execute statment to run
the query:

Dim strSQL As String
Dim strWhere As String

strSQL = "INSERT INTO tblQualChecklist ( Activity, Sequence, DocType ) "
& _
"SELECT " & Forms!frmnewactivity!Activity & " AS Activity, " & _
Forms!frmnewactivity!Sequence & " AS Sequence, DocType " & _
"FROM tblQualChecklist WHERE DocType = "

strWhere = BuildWhereCondition(Me.MyListBoxName.Name)

strSQL = strSQL & strWhere

Docmd.Execute(strSQL), dbFailOnError
--
Dave Hargis, Microsoft Access MVP


Rob said:
Finish_Click is the name of the public function. It is calling and and i
have set it so it "walks" through the code and it works but I am getting no
data.

Klatuu said:
What is Finish_Click()?

I don't think that is going to work.
--
Dave Hargis, Microsoft Access MVP


:

Sequence Activity DocType
42 Verify PDF (composite and recent revision) TR/ADDENDUM
63 Verify Work Request Scope TR/ADDENDUM
201 Verify applicable Team Review TR/ADDENDUM
202 Verify Engineer Review TR/ADDENDUM
203 Verify that kickoff notes TR/ADDENDUM
204 Verify that PDFs TR/ADDENDUM

Here is an example of the fields. There are 32 different doc types showing
in my list box. I want to be able to append to this table a new Sequence #
that corresponds to a new Activity for certain Doc Types.

The SQL and funcrtion run completed through, but I get no records appended
to the table.

You have helped me with other problems in the past and I am learning so much
by working with Access and through you and others in this forum. Thanks
Again!!!

Here is the SQL:

INSERT INTO tblQualChecklist ( Activity, Sequence, DocType )
SELECT Forms!frmnewactivity!Activity AS Activity,
Forms!frmnewactivity!Sequence AS Sequence, DocType
FROM tblQualChecklist
WHERE (((DocType)=Finish_Click()));


:

I don't understand the problem. The Me part you figured out. But, since you
don't have the code in the form module, it does need to be public or it can't
be seen outside the module it is in.

Can you post the error number and exact text of the error? It doesn't seem
to make sense to me.
--
Dave Hargis, Microsoft Access MVP


:

I thought I had figured out what to use instead of ME. I used this "set ctl
= Forms!frmnewactivity.lstDocType. This seems to work and run through the
function fine but when I get to the "End Function" it says that this can't be
used at this time. I tried to change the funciton to a private function
instead of public and it says that this function has not been defined.
Almost there. Please help!

:

By calling the BuildWhereCondition function and passing the control to it,
the return is a string with all the selections in it. You just need to add
the word Where in front of it. Then you use the where condition string in
your query to do the updates.
--
Dave Hargis, Microsoft Access MVP


:

Thanks so much for the quick response but I don't quite understand. Where do
I specify to update the table based on the data in the "Activity field" and
the"Sequence" field. And where do I mention my list box for looking up the
criteria.

:

Here is a handy function to do exactly that. It is set up so that if no
selections are made, then all are included. If you want the user to have to
select all, you would need some code behing the command button. See Below:

Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select

BuildWhereCondition = strWhere

End Function

Public Sub SelectAll()
Dim ctl As Control
Dim lngX As Long

Set ctl = Forms!MyForm!MyListBox
For lngX = 0 To ctl.ListCount - 1
ctl.Selected(lngX) = True
Next lngX
Set ctl = Nothing
End Sub

--
Dave Hargis, Microsoft Access MVP


:

I would like to create a query that would update a table based on the data
entered in two text boxes. The criteria for updating this table would be
based on a multiselect list box... So basically the query reads as such:


UPDATE tblQualChecklist SET tblQualChecklist.Sequence =
[Forms]![frmUpdateCheklist]![Sequence], tblQualChecklist.Activity =
[Forms]![frmUpdateCheklist]![Activity]
WHERE (((tblQualChecklist.DocType)=[Forms]![frmUpdateCheklist]![lstDocType]));


The "Where" clause is the list box. As of now I only get one record
updated. How do I get the query to update based on more than one selection
in the multi select list box.


Also, I would like to create a check box that would select all the items in
the multislect list box and update accordingly. I was thinking that I can
use the "Or is Null" extension on the "Where" clause in the query but I
believe my user's would prefer clicking a check box that would highlight all
the items in the list box and the query based on those selections.

Please Help!!
Click to expand...
Click to expand...
Click to expand...
 
Sorry about the docmd. It should be CurrentDb.Execute.
The rest of it should be okay, I think.
--
Dave Hargis, Microsoft Access MVP


Rob said:
I hope you don't mind me bothering you. This almost works but it doesn't
seem to like the "Docmd.Execute" I changed the name of the function to
BuildWhereCondition and have changed the Me. to
Forms!frmnewactivity.lstDocType.Name)

So here is what they look like:

Private Sub Finish_Click()

Dim strSQL As String
Dim strWhere As String

strSQL = "INSERT INTO tblQualChecklist ( Activity, Sequence, DocType ) "
& _
"SELECT " & Forms!frmnewactivity!Activity & " AS Activity, " & _
Forms!frmnewactivity!Sequence & " AS Sequence, DocType " & _
"FROM tblQualChecklist WHERE DocType = "

strWhere = BuildWhereCondition(Me.lstDocType.Name)

strSQL = strSQL & strWhere

DoCmd.Execute strSQL), dbFailOnError

Public Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select

BuildWhereCondition = strWhere

End Function





End Sub



Klatuu said:
Try changing the way you are doing it. Rather than use a stored query, build
the SQL statment in VBA. Put the results of the Finish_Click function into a
string variable and add it to the SQL. Then use the execute statment to run
the query:

Dim strSQL As String
Dim strWhere As String

strSQL = "INSERT INTO tblQualChecklist ( Activity, Sequence, DocType ) "
& _
"SELECT " & Forms!frmnewactivity!Activity & " AS Activity, " & _
Forms!frmnewactivity!Sequence & " AS Sequence, DocType " & _
"FROM tblQualChecklist WHERE DocType = "

strWhere = BuildWhereCondition(Me.MyListBoxName.Name)

strSQL = strSQL & strWhere

Docmd.Execute(strSQL), dbFailOnError
--
Dave Hargis, Microsoft Access MVP


Rob said:
Finish_Click is the name of the public function. It is calling and and i
have set it so it "walks" through the code and it works but I am getting no
data.

:

What is Finish_Click()?

I don't think that is going to work.
--
Dave Hargis, Microsoft Access MVP


:

Sequence Activity DocType
42 Verify PDF (composite and recent revision) TR/ADDENDUM
63 Verify Work Request Scope TR/ADDENDUM
201 Verify applicable Team Review TR/ADDENDUM
202 Verify Engineer Review TR/ADDENDUM
203 Verify that kickoff notes TR/ADDENDUM
204 Verify that PDFs TR/ADDENDUM

Here is an example of the fields. There are 32 different doc types showing
in my list box. I want to be able to append to this table a new Sequence #
that corresponds to a new Activity for certain Doc Types.

The SQL and funcrtion run completed through, but I get no records appended
to the table.

You have helped me with other problems in the past and I am learning so much
by working with Access and through you and others in this forum. Thanks
Again!!!

Here is the SQL:

INSERT INTO tblQualChecklist ( Activity, Sequence, DocType )
SELECT Forms!frmnewactivity!Activity AS Activity,
Forms!frmnewactivity!Sequence AS Sequence, DocType
FROM tblQualChecklist
WHERE (((DocType)=Finish_Click()));


:

I don't understand the problem. The Me part you figured out. But, since you
don't have the code in the form module, it does need to be public or it can't
be seen outside the module it is in.

Can you post the error number and exact text of the error? It doesn't seem
to make sense to me.
--
Dave Hargis, Microsoft Access MVP


:

I thought I had figured out what to use instead of ME. I used this "set ctl
= Forms!frmnewactivity.lstDocType. This seems to work and run through the
function fine but when I get to the "End Function" it says that this can't be
used at this time. I tried to change the funciton to a private function
instead of public and it says that this function has not been defined.
Almost there. Please help!

:

By calling the BuildWhereCondition function and passing the control to it,
the return is a string with all the selections in it. You just need to add
the word Where in front of it. Then you use the where condition string in
your query to do the updates.
--
Dave Hargis, Microsoft Access MVP


:

Thanks so much for the quick response but I don't quite understand. Where do
I specify to update the table based on the data in the "Activity field" and
the"Sequence" field. And where do I mention my list box for looking up the
criteria.

:

Here is a handy function to do exactly that. It is set up so that if no
selections are made, then all are included. If you want the user to have to
select all, you would need some code behing the command button. See Below:

Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select

BuildWhereCondition = strWhere

End Function

Public Sub SelectAll()
Dim ctl As Control
Dim lngX As Long

Set ctl = Forms!MyForm!MyListBox
For lngX = 0 To ctl.ListCount - 1
ctl.Selected(lngX) = True
Next lngX
Set ctl = Nothing
End Sub

--
Dave Hargis, Microsoft Access MVP


:

I would like to create a query that would update a table based on the data
entered in two text boxes. The criteria for updating this table would be
based on a multiselect list box... So basically the query reads as such:


UPDATE tblQualChecklist SET tblQualChecklist.Sequence =
[Forms]![frmUpdateCheklist]![Sequence], tblQualChecklist.Activity =
[Forms]![frmUpdateCheklist]![Activity]
WHERE (((tblQualChecklist.DocType)=[Forms]![frmUpdateCheklist]![lstDocType]));


The "Where" clause is the list box. As of now I only get one record
updated. How do I get the query to update based on more than one selection
in the multi select list box.


Also, I would like to create a check box that would select all the items in
the multislect list box and update accordingly. I was thinking that I can
use the "Or is Null" extension on the "Where" clause in the query but I
believe my user's would prefer clicking a check box that would highlight all
the items in the list box and the query based on those selections.

Please Help!!
Click to expand...
Click to expand...
Click to expand...
 
Rob said:
I hope you don't mind me bothering you. This almost works but it doesn't
seem to like the "Docmd.Execute"
Click to expand...

Looks like Klatuu typed the wrong object name when he posted his suggested
code. Instead of "DoCmd.Execute", Use "CurrentDb.Execute".
 
Well all runs well until the last command:
Private Sub Finish_Click()
Dim strSQL As String
Dim strWhere As String

strSQL = "INSERT INTO tblQualChecklist ( Activity, Sequence, DocType ) "
& _
"SELECT " & Forms!frmnewactivity!Activity & " AS Activity, " & _
Forms!frmnewactivity!Sequence & " AS Sequence, DocType " & _
"FROM tblQualChecklist WHERE DocType = BuildWhereConditon"

strWhere = BuildWhereCondition(Forms!frmnewactivity.lstDocType.Name)

strSQL = strSQL & strWhere

CurrentDb.Execute (strSQL), dbFailOnError

I get an error stating that "CurrentDb.Execute (strSQL), dbFailOnError" has
two few paraments Expected 2
 
Assumptions:
--Activity is a text field.
--Sequence is a number field.
--BuildWhereCondition builds a complete where string including the word
WHERE

strSQL = "INSERT INTO tblQualChecklist ( Activity, Sequence, DocType ) " & _
"SELECT """ & Forms!frmnewactivity!Activity & """ AS Activity, " & _
Forms!frmnewactivity!Sequence & " AS Sequence, DocType " & _
"FROM tblQualChecklist"

strWhere = BuildWhereCondition(Forms!frmnewactivity.lstDocType.Name)

strSQL = strSQL & strWhere
Debug.Print strSQL 'Add this line for debug purposes.
'If the SQL fails to run properly you can examine the print out
'in the VBA immediate window to see what is incorrect.
'You can actually, copy the SQL and paste it into a new query for
'troubleshooting.
CurrentDb.Execute (strSQL), dbFailOnError



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Run your code in debug mode. Put a breakpoint on the Execute line. Then
when the code stops there, look at the value of strSQL and see if it is what
you expect.
 
Klatuu:

I don't get any results for the code breaks at

"CurrentDb.Execute (strSQL), dbFailOnError"

Klatuu said:
Sorry about the docmd. It should be CurrentDb.Execute.
The rest of it should be okay, I think.
--
Dave Hargis, Microsoft Access MVP


Rob said:
I hope you don't mind me bothering you. This almost works but it doesn't
seem to like the "Docmd.Execute" I changed the name of the function to
BuildWhereCondition and have changed the Me. to
Forms!frmnewactivity.lstDocType.Name)

So here is what they look like:

Private Sub Finish_Click()

Dim strSQL As String
Dim strWhere As String

strSQL = "INSERT INTO tblQualChecklist ( Activity, Sequence, DocType ) "
& _
"SELECT " & Forms!frmnewactivity!Activity & " AS Activity, " & _
Forms!frmnewactivity!Sequence & " AS Sequence, DocType " & _
"FROM tblQualChecklist WHERE DocType = "

strWhere = BuildWhereCondition(Me.lstDocType.Name)

strSQL = strSQL & strWhere

DoCmd.Execute strSQL), dbFailOnError

Public Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select

BuildWhereCondition = strWhere

End Function





End Sub



Klatuu said:
Try changing the way you are doing it. Rather than use a stored query, build
the SQL statment in VBA. Put the results of the Finish_Click function into a
string variable and add it to the SQL. Then use the execute statment to run
the query:

Dim strSQL As String
Dim strWhere As String

strSQL = "INSERT INTO tblQualChecklist ( Activity, Sequence, DocType ) "
& _
"SELECT " & Forms!frmnewactivity!Activity & " AS Activity, " & _
Forms!frmnewactivity!Sequence & " AS Sequence, DocType " & _
"FROM tblQualChecklist WHERE DocType = "

strWhere = BuildWhereCondition(Me.MyListBoxName.Name)

strSQL = strSQL & strWhere

Docmd.Execute(strSQL), dbFailOnError
--
Dave Hargis, Microsoft Access MVP


:

Finish_Click is the name of the public function. It is calling and and i
have set it so it "walks" through the code and it works but I am getting no
data.

:

What is Finish_Click()?

I don't think that is going to work.
--
Dave Hargis, Microsoft Access MVP


:

Sequence Activity DocType
42 Verify PDF (composite and recent revision) TR/ADDENDUM
63 Verify Work Request Scope TR/ADDENDUM
201 Verify applicable Team Review TR/ADDENDUM
202 Verify Engineer Review TR/ADDENDUM
203 Verify that kickoff notes TR/ADDENDUM
204 Verify that PDFs TR/ADDENDUM

Here is an example of the fields. There are 32 different doc types showing
in my list box. I want to be able to append to this table a new Sequence #
that corresponds to a new Activity for certain Doc Types.

The SQL and funcrtion run completed through, but I get no records appended
to the table.

You have helped me with other problems in the past and I am learning so much
by working with Access and through you and others in this forum. Thanks
Again!!!

Here is the SQL:

INSERT INTO tblQualChecklist ( Activity, Sequence, DocType )
SELECT Forms!frmnewactivity!Activity AS Activity,
Forms!frmnewactivity!Sequence AS Sequence, DocType
FROM tblQualChecklist
WHERE (((DocType)=Finish_Click()));


:

I don't understand the problem. The Me part you figured out. But, since you
don't have the code in the form module, it does need to be public or it can't
be seen outside the module it is in.

Can you post the error number and exact text of the error? It doesn't seem
to make sense to me.
--
Dave Hargis, Microsoft Access MVP


:

I thought I had figured out what to use instead of ME. I used this "set ctl
= Forms!frmnewactivity.lstDocType. This seems to work and run through the
function fine but when I get to the "End Function" it says that this can't be
used at this time. I tried to change the funciton to a private function
instead of public and it says that this function has not been defined.
Almost there. Please help!

:

By calling the BuildWhereCondition function and passing the control to it,
the return is a string with all the selections in it. You just need to add
the word Where in front of it. Then you use the where condition string in
your query to do the updates.
--
Dave Hargis, Microsoft Access MVP


:

Thanks so much for the quick response but I don't quite understand. Where do
I specify to update the table based on the data in the "Activity field" and
the"Sequence" field. And where do I mention my list box for looking up the
criteria.

:

Here is a handy function to do exactly that. It is set up so that if no
selections are made, then all are included. If you want the user to have to
select all, you would need some code behing the command button. See Below:

Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select

BuildWhereCondition = strWhere

End Function

Public Sub SelectAll()
Dim ctl As Control
Dim lngX As Long

Set ctl = Forms!MyForm!MyListBox
For lngX = 0 To ctl.ListCount - 1
ctl.Selected(lngX) = True
Next lngX
Set ctl = Nothing
End Sub

--
Dave Hargis, Microsoft Access MVP


:

I would like to create a query that would update a table based on the data
entered in two text boxes. The criteria for updating this table would be
based on a multiselect list box... So basically the query reads as such:


UPDATE tblQualChecklist SET tblQualChecklist.Sequence =
[Forms]![frmUpdateCheklist]![Sequence], tblQualChecklist.Activity =
[Forms]![frmUpdateCheklist]![Activity]
WHERE (((tblQualChecklist.DocType)=[Forms]![frmUpdateCheklist]![lstDocType]));


The "Where" clause is the list box. As of now I only get one record
updated. How do I get the query to update based on more than one selection
in the multi select list box.


Also, I would like to create a check box that would select all the items in
the multislect list box and update accordingly. I was thinking that I can
use the "Or is Null" extension on the "Where" clause in the query but I
believe my user's would prefer clicking a check box that would highlight all
the items in the list box and the query based on those selections.

Please Help!!
Click to expand...
Click to expand...
Click to expand...
 
Try

CurrentDb.Execute strSQL, dbFailOnError

Drop the parentheses
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Rob said:
Klatuu:

I don't get any results for the code breaks at

"CurrentDb.Execute (strSQL), dbFailOnError"

Klatuu said:
Sorry about the docmd. It should be CurrentDb.Execute.
The rest of it should be okay, I think.
--
Dave Hargis, Microsoft Access MVP


Rob said:
I hope you don't mind me bothering you. This almost works but it
doesn't
seem to like the "Docmd.Execute" I changed the name of the function to
BuildWhereCondition and have changed the Me. to
Forms!frmnewactivity.lstDocType.Name)

So here is what they look like:

Private Sub Finish_Click()

Dim strSQL As String
Dim strWhere As String

strSQL = "INSERT INTO tblQualChecklist ( Activity, Sequence,
DocType ) "
& _
"SELECT " & Forms!frmnewactivity!Activity & " AS Activity, " &
_
Forms!frmnewactivity!Sequence & " AS Sequence, DocType " & _
"FROM tblQualChecklist WHERE DocType = "

strWhere = BuildWhereCondition(Me.lstDocType.Name)

strSQL = strSQL & strWhere

DoCmd.Execute strSQL), dbFailOnError

Public Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) &
"', "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select

BuildWhereCondition = strWhere

End Function





End Sub



:

Try changing the way you are doing it. Rather than use a stored
query, build
the SQL statment in VBA. Put the results of the Finish_Click
function into a
string variable and add it to the SQL. Then use the execute statment
to run
the query:

Dim strSQL As String
Dim strWhere As String

strSQL = "INSERT INTO tblQualChecklist ( Activity, Sequence,
DocType ) "
& _
"SELECT " & Forms!frmnewactivity!Activity & " AS Activity, "
& _
Forms!frmnewactivity!Sequence & " AS Sequence, DocType " & _
"FROM tblQualChecklist WHERE DocType = "

strWhere = BuildWhereCondition(Me.MyListBoxName.Name)

strSQL = strSQL & strWhere

Docmd.Execute(strSQL), dbFailOnError
--
Dave Hargis, Microsoft Access MVP


:

Finish_Click is the name of the public function. It is calling and
and i
have set it so it "walks" through the code and it works but I am
getting no
data.

:

What is Finish_Click()?

I don't think that is going to work.
--
Dave Hargis, Microsoft Access MVP


:

Sequence Activity
DocType
42 Verify PDF (composite and recent revision) TR/ADDENDUM
63 Verify Work Request Scope TR/ADDENDUM
201 Verify applicable Team Review TR/ADDENDUM
202 Verify Engineer Review TR/ADDENDUM
203 Verify that kickoff notes TR/ADDENDUM
204 Verify that PDFs
TR/ADDENDUM

Here is an example of the fields. There are 32 different doc
types showing
in my list box. I want to be able to append to this table a
new Sequence #
that corresponds to a new Activity for certain Doc Types.

The SQL and funcrtion run completed through, but I get no
records appended
to the table.

You have helped me with other problems in the past and I am
learning so much
by working with Access and through you and others in this
forum. Thanks
Again!!!

Here is the SQL:

INSERT INTO tblQualChecklist ( Activity, Sequence, DocType )
SELECT Forms!frmnewactivity!Activity AS Activity,
Forms!frmnewactivity!Sequence AS Sequence, DocType
FROM tblQualChecklist
WHERE (((DocType)=Finish_Click()));


:

I don't understand the problem. The Me part you figured out.
But, since you
don't have the code in the form module, it does need to be
public or it can't
be seen outside the module it is in.

Can you post the error number and exact text of the error?
It doesn't seem
to make sense to me.
--
Dave Hargis, Microsoft Access MVP


:

I thought I had figured out what to use instead of ME. I
used this "set ctl
= Forms!frmnewactivity.lstDocType. This seems to work and
run through the
function fine but when I get to the "End Function" it says
that this can't be
used at this time. I tried to change the funciton to a
private function
instead of public and it says that this function has not
been defined.
Almost there. Please help!

:

By calling the BuildWhereCondition function and passing
the control to it,
the return is a string with all the selections in it.
You just need to add
the word Where in front of it. Then you use the where
condition string in
your query to do the updates.
--
Dave Hargis, Microsoft Access MVP


:

Thanks so much for the quick response but I don't quite
understand. Where do
I specify to update the table based on the data in the
"Activity field" and
the"Sequence" field. And where do I mention my list
box for looking up the
criteria.

:

Here is a handy function to do exactly that. It is
set up so that if no
selections are made, then all are included. If you
want the user to have to
select all, you would need some code behing the
command button. See Below:

Private Function BuildWhereCondition(strControl As
String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) &
"'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" &
.ItemData(varItem) & "', "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) -
2) & ")"
End Select

BuildWhereCondition = strWhere

End Function

Public Sub SelectAll()
Dim ctl As Control
Dim lngX As Long

Set ctl = Forms!MyForm!MyListBox
For lngX = 0 To ctl.ListCount - 1
ctl.Selected(lngX) = True
Next lngX
Set ctl = Nothing
End Sub

--
Dave Hargis, Microsoft Access MVP


:

I would like to create a query that would update a
table based on the data
entered in two text boxes. The criteria for
updating this table would be
based on a multiselect list box... So basically
the query reads as such:


UPDATE tblQualChecklist SET
tblQualChecklist.Sequence =
[Forms]![frmUpdateCheklist]![Sequence],
tblQualChecklist.Activity =
[Forms]![frmUpdateCheklist]![Activity]
WHERE
(((tblQualChecklist.DocType)=[Forms]![frmUpdateCheklist]![lstDocType]));


The "Where" clause is the list box. As of now I
only get one record
updated. How do I get the query to update based on
more than one selection
in the multi select list box.


Also, I would like to create a check box that would
select all the items in
the multislect list box and update accordingly. I
was thinking that I can
use the "Or is Null" extension on the "Where"
clause in the query but I
believe my user's would prefer clicking a check box
that would highlight all
the items in the list box and the query based on
those selections.

Please Help!!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top