Update table based on combobox

[lecoughlin]

I have a form, with a combobox that lists every county in a state.
This is populated from a table

A user selects multiple counties that are covered, and I'd like to
then be able to update the table based on the user's selection. How
would I do this in VBA?

Thanks in advance.

 

[ken]

A combo box can only have one value at a time. If you want to select
multiple values simultaneously then you'll need to use a multi-select
list box. You'd then need to step through its ItemsSelected collection
in code and update the table item by item using SQL, DAO or ADO.

Which table do you want to update, and with what? At the moment you
are telling us how you want to do something not what you want to do.
If you can give us detailed information about what you want to achieve
in terms of the real world entities modelled by the database, then
we'll be in a better position to help you with the 'how'.

Ken Sheridan
Stafford, England

 

[Arvin Meyer MVP]

Here's some aircode that should get you started:

Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset ("YourTableName", dbOpenDynaset)

With Me.lstWhatever

For Each varItem In .ItemsSelected
With rst
.Edit
!Value = & Me.ValueFromTheForm
.Update
End With
Next varItem

End With

Exit_Here:
rst.Close
Set rst = Nothing
Set db = Nothing

 

[lecoughlin]

Sorry, I have a multi-select list box. The data in this box comes
from tblPopulation, which contains variables for state, county,
population, and a text field [covered] which indicates the siteID
associated with that county (to indicate whether or not it is
covered).

On the form, the user first selects the state, which then causes the
list box (List38) to populate only with the counties for that state.
They will then click on which counties are covered.

What I'd like to do, is make sure that if the user has selected a
county, that the field [covered] in tblPopulation is updated to
contain the SiteID.
note: qry_Population is essentially the same as the table, only it has
a combined field for state and county - for ex: "Massachusetts: Essex
County"

I've figured out how to point to the field I'm interested in, but not
how to actually change it:

With Forms![form1]!List38
For i = 0 To .ListCount - 1
If .Selected(i) Then
statecounty = newProgramState & ": " & .Column(2, i)
MsgBox (DLookup("[covered]", "qry_Population", "[stcty] =
'" & statecounty & "'"))
End If
Next i
End With

I hope that is more clear, thanks in advance for your help.

 

[ken]

I'm still not clear just want you are trying to achieve but you'd
probably want something along these lines:

Dim cmd As ADODB.Command
Dim strSQL As String
Dim varItem As Variant
Dim ctrl As Control

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

Set ctrl = Me.List38

If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strSQL = "UPDATE tblPopulation " & _
"SET covered = """ & Me.SiteID & """" & _
"WHERE county = """ & ctrl.ItemData(varItem) & """"

cmd.CommandText = strSQL
cmd.Execute
Next varItem
Else
MsgBox "No counties selected", vbInformation, "Warning"
End If

This assumes that the covered column in the tblPopulation table is to
be updated in each row where the value of the county column is one of
those selected in the list box, updating the column with a string
value of SiteID which is a control in the current form. Both covered
and county are assumed to be columns of text data type.

However, I'm having to make a number of leaps in the dark here as you
don't say where the value of SiteID is coming from (so I've assumed
it’s a control, bound or otherwise, in the form). More fundamentally,
I'm not entirely clear whether by 'update' you are using this term in
the usual sense of changing a value in an existing row in a table
(which I've assumed above) or in the more generic sense in which it is
often used in the literature, covering any changes, including the
insertion of a row, the amendment of a value or values, and even the
deletion of a row.

We can of course rule out deletion of a row, but if you want to insert
a row then the SQL statement built in the above code would need to be
amended so that it does that rather than amending an existing row.

Ken Sheridan
Stafford, England

Sorry, I have a multi-select list box. The data in this box comes
from tblPopulation, which contains variables for state, county,
population, and a text field [covered] which indicates the siteID
associated with that county (to indicate whether or not it is
covered).

On the form, the user first selects the state, which then causes the
list box (List38) to populate only with the counties for that state.
They will then click on which counties are covered.

What I'd like to do, is make sure that if the user has selected a
county, that the field [covered] in tblPopulation is updated to
contain the SiteID.
note: qry_Population is essentially the same as the table, only it has
a combined field for state and county - for ex: "Massachusetts: Essex
County"

I've figured out how to point to the field I'm interested in, but not
how to actually change it:

With Forms![form1]!List38
For i = 0 To .ListCount - 1
If .Selected(i) Then
statecounty = newProgramState & ": " & .Column(2, i)
MsgBox (DLookup("[covered]", "qry_Population", "[stcty] =
'" & statecounty & "'"))
End If
Next i
End With

I hope that is more clear, thanks in advance for your help.

A combo box can only have one value at a time. If you want to select
multiple values simultaneously then you'll need to use a multi-select
list box. You'd then need to step through its ItemsSelected collection
in code and update the table item by item using SQL, DAO or ADO.

Which table do you want to update, and with what? At the moment you
are telling us how you want to do something not what you want to do.
If you can give us detailed information about what you want to achieve
in terms of the real world entities modelled by the database, then
we'll be in a better position to help you with the 'how'.

Ken Sheridan
Stafford, England

On May 7, 4:33 pm, (e-mail address removed) wrote:

 

[lecoughlin]

Hi Ken, this is perfect, thanks. All of your assumptions are correct.

My only question is, how do I reference the actual state and county
name?

"WHERE county = """ & ctrl.ItemData(varItem) & """"

ctrl.ItemData(varItem) returns a #, but I need to get at the actual
county name. Thanks

I'm still not clear just want you are trying to achieve but you'd
probably want something along these lines:

    Dim cmd As ADODB.Command
    Dim strSQL As String
    Dim varItem As Variant
    Dim ctrl As Control

    Set cmd = New ADODB.Command
    cmd.ActiveConnection = CurrentProject.Connection
    cmd.CommandType = adCmdText

    Set ctrl = Me.List38

    If ctrl.ItemsSelected.Count > 0 Then
        For Each varItem In ctrl.ItemsSelected
            strSQL = "UPDATE tblPopulation " & _
                "SET  covered = """ & Me.SiteID & """" & _
                "WHERE county = """ & ctrl.ItemData(varItem) & """"

            cmd.CommandText = strSQL
            cmd.Execute
        Next varItem
    Else
        MsgBox "No counties selected", vbInformation, "Warning"
    End If

This assumes that the covered column in the tblPopulation table is to
be updated in each row where the value of the county column is one of
those selected in the list box,  updating the column with a string
value of SiteID which is a control in the current form.  Both covered
and county are assumed to be columns of text data type.

However, I'm having to make a number of leaps in the dark here as you
don't say where the value of SiteID is coming from (so I've assumed
it’s a control, bound or otherwise, in the form).  More fundamentally,
I'm not entirely clear whether by 'update' you are using this term in
the usual sense of changing a value in an existing row in a table
(which I've assumed above) or in the more generic sense in which it is
often used in the literature, covering any changes, including the
insertion of a row, the amendment of a value or values, and even the
deletion of a row.

We can of course rule out deletion of a row, but if you want to insert
a row then the SQL statement built in the above code would need to be
amended so that it does that rather than amending an existing row.

Ken Sheridan
Stafford, England

Sorry, I have a multi-select list box.  The data in this box comes
from tblPopulation, which contains variables for state, county,
population, and a text field [covered] which indicates the siteID
associated with that county (to indicate whether or not it is
covered).

On the form, the user first selects the state, which then causes the
list box (List38) to populate only with the counties for that state.
They will then click on which counties are covered.

What I'd like to do, is make sure that if the user has selected a
county, that the field [covered] in tblPopulation is updated to
contain the SiteID.
note: qry_Population is essentially the same as the table, only it has
a combined field for state and county - for ex: "Massachusetts: Essex
County"

I've figured out how to point to the field I'm interested in, but not
how to actually change it:

With Forms![form1]!List38
    For i = 0 To .ListCount - 1
        If .Selected(i) Then
            statecounty = newProgramState & ": " & .Column(2, i)
            MsgBox (DLookup("[covered]", "qry_Population", "[stcty] =
'" & statecounty & "'"))
        End If
    Next i
End With

I hope that is more clear, thanks in advance for your help.

On May 7, 11:47 am, (e-mail address removed) wrote:

 

[lecoughlin]

Figured it out... thanks so much for your help!

Hi Ken, this is perfect, thanks.  All of your assumptions are correct.

My only question is, how do I reference the actual state and county
name?

"WHERE county = """ & ctrl.ItemData(varItem) & """"

ctrl.ItemData(varItem) returns a #, but I need to get at the actual
county name.  Thanks

I'm still not clear just want you are trying to achieve but you'd
probably want something along these lines:

    Dim cmd As ADODB.Command
    Dim strSQL As String
    Dim varItem As Variant
    Dim ctrl As Control

    Set cmd = New ADODB.Command
    cmd.ActiveConnection = CurrentProject.Connection
    cmd.CommandType = adCmdText

    Set ctrl = Me.List38

    If ctrl.ItemsSelected.Count > 0 Then
        For Each varItem In ctrl.ItemsSelected
            strSQL = "UPDATE tblPopulation " & _
                "SET  covered = """ & Me.SiteID & """" & _
                "WHERE county = """ & ctrl.ItemData(varItem) & """"

            cmd.CommandText = strSQL
            cmd.Execute
        Next varItem
    Else
        MsgBox "No counties selected", vbInformation, "Warning"
    End If

This assumes that the covered column in the tblPopulation table is to
be updated in each row where the value of the county column is one of
those selected in the list box,  updating the column with a string
value of SiteID which is a control in the current form.  Both covered
and county are assumed to be columns of text data type.

However, I'm having to make a number of leaps in the dark here as you
don't say where the value of SiteID is coming from (so I've assumed
it’s a control, bound or otherwise, in the form).  More fundamentally,
I'm not entirely clear whether by 'update' you are using this term in
the usual sense of changing a value in an existing row in a table
(which I've assumed above) or in the more generic sense in which it is
often used in the literature, covering any changes, including the
insertion of a row, the amendment of a value or values, and even the
deletion of a row.

We can of course rule out deletion of a row, but if you want to insert
a row then the SQL statement built in the above code would need to be
amended so that it does that rather than amending an existing row.

Ken Sheridan
Stafford, England

On May 7, 5:05 pm, (e-mail address removed) wrote:

Sorry, I have a multi-select list box.  The data in this box comes
from tblPopulation, which contains variables for state, county,
population, and a text field [covered] which indicates the siteID
associated with that county (to indicate whether or not it is
covered).
On the form, the user first selects the state, which then causes the
list box (List38) to populate only with the counties for that state.
They will then click on which counties are covered.
What I'd like to do, is make sure that if the user has selected a
county, that the field [covered] in tblPopulation is updated to
contain the SiteID.
note: qry_Population is essentially the same as the table, only it has
a combined field for state and county - for ex: "Massachusetts: Essex
County"
I've figured out how to point to the field I'm interested in, but not
how to actually change it:
With Forms![form1]!List38
    For i = 0 To .ListCount - 1
        If .Selected(i) Then
            statecounty = newProgramState & ": " & .Column(2, i)
            MsgBox (DLookup("[covered]", "qry_Population", "[stcty] =
'" & statecounty & "'"))
        End If
    Next i
End With
I hope that is more clear, thanks in advance for your help.
On May 7, 11:47 am, (e-mail address removed) wrote:
A combo box can only have one value at a time.  If you want to select
multiple values simultaneously then you'll need to use a multi-select
list box. You'd then need to step through its ItemsSelected collection
in code and update the table item by item using SQL, DAO or ADO.
Which table do you want to update, and with what?  At the moment you
are telling us how you want to do something not what you want to do..
If you can give us detailed information about what you want to achieve
in terms of the real world entities modelled by the database, then
we'll be in a better position to help you with the 'how'.
Ken Sheridan
Stafford, England
On May 7, 4:33 pm, (e-mail address removed) wrote:
I have a form, with a combobox that lists every county in a state..
This is populated from a table
A user selects multiple counties that are covered, and I'd like to
then be able to update the table based on the user's selection.  How
would I do this in VBA?
Thanks in advance.

 

[lecoughlin]

Actually one more thing, how would I incorporate code to check if the
field [covered] is empty, and if not have something pop up asking if
we want to overwrite it?

Thanks.

Figured it out... thanks so much for your help!

Hi Ken, this is perfect, thanks.  All of your assumptions are correct..

My only question is, how do I reference the actual state and county
name?

"WHERE county = """ & ctrl.ItemData(varItem) & """"

ctrl.ItemData(varItem) returns a #, but I need to get at the actual
county name.  Thanks

On May 7, 12:47 pm, (e-mail address removed) wrote:

I'm still not clear just want you are trying to achieve but you'd
probably want something along these lines:
    Dim cmd As ADODB.Command
    Dim strSQL As String
    Dim varItem As Variant
    Dim ctrl As Control
    Set cmd = New ADODB.Command
    cmd.ActiveConnection = CurrentProject.Connection
    cmd.CommandType = adCmdText
    Set ctrl = Me.List38
    If ctrl.ItemsSelected.Count > 0 Then
        For Each varItem In ctrl.ItemsSelected
            strSQL = "UPDATE tblPopulation " & _
                "SET  covered = """ & Me.SiteID &"""" & _
                "WHERE county = """ & ctrl.ItemData(varItem) & """"
            cmd.CommandText = strSQL
            cmd.Execute
        Next varItem
    Else
        MsgBox "No counties selected", vbInformation, "Warning"
    End If
This assumes that the covered column in the tblPopulation table is to
be updated in each row where the value of the county column is one of
those selected in the list box,  updating the column with a string
value of SiteID which is a control in the current form.  Both covered
and county are assumed to be columns of text data type.
However, I'm having to make a number of leaps in the dark here as you
don't say where the value of SiteID is coming from (so I've assumed
it’s a control, bound or otherwise, in the form).  More fundamentally,
I'm not entirely clear whether by 'update' you are using this term in
the usual sense of changing a value in an existing row in a table
(which I've assumed above) or in the more generic sense in which it is
often used in the literature, covering any changes, including the
insertion of a row, the amendment of a value or values, and even the
deletion of a row.
We can of course rule out deletion of a row, but if you want to insert
a row then the SQL statement built in the above code would need to be
amended so that it does that rather than amending an existing row.
Ken Sheridan
Stafford, England
On May 7, 5:05 pm, (e-mail address removed) wrote:
Sorry, I have a multi-select list box.  The data in this box comes
from tblPopulation, which contains variables for state, county,
population, and a text field [covered] which indicates the siteID
associated with that county (to indicate whether or not it is
covered).
On the form, the user first selects the state, which then causes the
list box (List38) to populate only with the counties for that state..
They will then click on which counties are covered.
What I'd like to do, is make sure that if the user has selected a
county, that the field [covered] in tblPopulation is updated to
contain the SiteID.
note: qry_Population is essentially the same as the table, only it has
a combined field for state and county - for ex: "Massachusetts: Essex
County"
I've figured out how to point to the field I'm interested in, but not
how to actually change it:
With Forms![form1]!List38
    For i = 0 To .ListCount - 1
        If .Selected(i) Then
            statecounty = newProgramState & ": " & .Column(2, i)
            MsgBox (DLookup("[covered]", "qry_Population", "[stcty] =
'" & statecounty & "'"))
        End If
    Next i
End With
I hope that is more clear, thanks in advance for your help.
On May 7, 11:47 am, (e-mail address removed) wrote:
A combo box can only have one value at a time.  If you want to select
multiple values simultaneously then you'll need to use a multi-select
list box. You'd then need to step through its ItemsSelected collection
in code and update the table item by item using SQL, DAO or ADO.
Which table do you want to update, and with what?  At the moment you
are telling us how you want to do something not what you want to do.
If you can give us detailed information about what you want to achieve
in terms of the real world entities modelled by the database, then
we'll be in a better position to help you with the 'how'.
Ken Sheridan
Stafford, England
On May 7, 4:33 pm, (e-mail address removed) wrote:
I have a form, with a combobox that lists every county in a state.
This is populated from a table
A user selects multiple counties that are covered, and I'd liketo
then be able to update the table based on the user's selection. How
would I do this in VBA?
Thanks in advance.

 

[ken]

Try this. You'll presumably need to amend the line which assigns a
value to the strMessage variable so that it returns the county name
not its numeric key value, but as you've already done that elsewhere
you can doubtless do it in whatever way you used there.

Dim cmd As ADODB.Command
Dim strSQL As String
Dim strCriteria As String
Dim strMessage As String
Dim varItem As Variant
Dim varCovered as Variant
Dim ctrl As Control

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

Set ctrl = Me.List38

If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strCriteria = county = """ & ctrl.ItemData(varItem) &
""""

strSQL = "UPDATE tblPopulation " & _
"SET covered = """ & Me.SiteID & """" & _
"WHERE & strCriteria

varCovered = Dlookup("covered", "tblPopulation",
strCriteria)

cmd.CommandText = strSQL

If IsNull(varCovered) Then
cmd.Execute
Else
strMessage = "Overwite value '" & _
varCovered "' for county " & ctrl.ItemData
(varItem)
If MsgBox(strMessage, vbQuestion + vbYesno,"Confirm")
= vbYes
cmd.Execute
End If
End If
Next varItem
Else
MsgBox "No counties selected", vbInformation, "Warning"
End If

Ken Sheridan
Stafford, England

Actually one more thing, how would I incorporate code to check if the
field [covered] is empty, and if not have something pop up asking if
we want to overwrite it?

Thanks.

Figured it out... thanks so much for your help!

On May 7, 1:50 pm, (e-mail address removed) wrote:

Hi Ken, this is perfect, thanks. All of your assumptions are correct..
My only question is, how do I reference the actual state and county
name?
"WHERE county = """ & ctrl.ItemData(varItem) & """"
ctrl.ItemData(varItem) returns a #, but I need to get at the actual
county name. Thanks
On May 7, 12:47 pm, (e-mail address removed) wrote:
I'm still not clear just want you are trying to achieve but you'd
probably want something along these lines:
Dim cmd As ADODB.Command
Dim strSQL As String
Dim varItem As Variant
Dim ctrl As Control
Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText
Set ctrl = Me.List38
If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strSQL = "UPDATE tblPopulation " & _
"SET covered = """ & Me.SiteID & """" & _
"WHERE county = """ & ctrl.ItemData(varItem) & """"
cmd.CommandText = strSQL
cmd.Execute
Next varItem
Else
MsgBox "No counties selected", vbInformation, "Warning"
End If
This assumes that the covered column in the tblPopulation table is to
be updated in each row where the value of the county column is one of
those selected in the list box, updating the column with a string
value of SiteID which is a control in the current form. Both covered
and county are assumed to be columns of text data type.
However, I'm having to make a number of leaps in the dark here as you
don't say where the value of SiteID is coming from (so I've assumed
it’s a control, bound or otherwise, in the form). More fundamentally,
I'm not entirely clear whether by 'update' you are using this term in
the usual sense of changing a value in an existing row in a table
(which I've assumed above) or in the more generic sense in which itis
often used in the literature, covering any changes, including the
insertion of a row, the amendment of a value or values, and even the
deletion of a row.
We can of course rule out deletion of a row, but if you want to insert
a row then the SQL statement built in the above code would need to be
amended so that it does that rather than amending an existing row.
Ken Sheridan
Stafford, England
On May 7, 5:05 pm, (e-mail address removed) wrote:
Sorry, I have a multi-select list box. The data in this box comes
from tblPopulation, which contains variables for state, county,
population, and a text field [covered] which indicates the siteID
associated with that county (to indicate whether or not it is
covered).
On the form, the user first selects the state, which then causes the
list box (List38) to populate only with the counties for that state.
They will then click on which counties are covered.
What I'd like to do, is make sure that if the user has selected a
county, that the field [covered] in tblPopulation is updated to
contain the SiteID.
note: qry_Population is essentially the same as the table, only it has
a combined field for state and county - for ex: "Massachusetts: Essex
County"
I've figured out how to point to the field I'm interested in, butnot
how to actually change it:
With Forms![form1]!List38
For i = 0 To .ListCount - 1
If .Selected(i) Then
statecounty = newProgramState & ": " & .Column(2, i)
MsgBox (DLookup("[covered]", "qry_Population", "[stcty] =
'" & statecounty & "'"))
End If
Next i
End With
I hope that is more clear, thanks in advance for your help.
On May 7, 11:47 am, (e-mail address removed) wrote:
A combo box can only have one value at a time. If you want to select
multiple values simultaneously then you'll need to use a multi-select
list box. You'd then need to step through its ItemsSelected collection
in code and update the table item by item using SQL, DAO or ADO..
Which table do you want to update, and with what? At the moment you
are telling us how you want to do something not what you want to do.
If you can give us detailed information about what you want to achieve
in terms of the real world entities modelled by the database, then
we'll be in a better position to help you with the 'how'.
Ken Sheridan
Stafford, England
On May 7, 4:33 pm, (e-mail address removed) wrote:
I have a form, with a combobox that lists every county in a state.
This is populated from a table
A user selects multiple counties that are covered, and I'd like to
then be able to update the table based on the user's selection. How
would I do this in VBA?
Thanks in advance.

 

[caraffiemilio.]

I have a form, with a combobox that lists every county in a state.
This is populated from a table

A user selects multiple counties that are covered, and I'd like to
then be able to update the table based on the user's selection. How
would I do this in VBA?

Thanks in advance.