VBA for populating a subform from data in a listbox

[nax009]

Hi, I need help with populating a subform from data in a listbox which contain data from a querry: q_sum_A
Hereis data example from q_sum_A:

CODE (Short text)	VALUE (Number)	NOTE (Long text)
A 00 01 000	27.18	RC Footing
A 02 00 000	0.88	RC Slab
A 04 01 001	770	RC Collumn

Here is my form look like:

The main form contains: ListboxA (Data from q_sum_A querry), cmd_add button and a sub form: SubformA
I need some help working with VBA with cmd_add button.

I have tried this but it not working:

Dim ctrl As Control
Dim strsql As String
Dim varitem As Variant

Set ctrl = Me.ListboxA

If ctrl.ItemsSelected.Count > 0 Then
    For Each varitem In ctrl.ItemsSelected
        strsql = "INSERT INTO t_cost_fix(LinkID, CODE, VALUE, NOTE)" & _
                    "VALUES(" & Me.LinkID* & "," & ctrl.ItemData(varitem) & ")"
        CurrentDb.Execute strsql, dbFailOnError
    Next varitem
Else: MsgBox "Please select an item in the listbox", vbInformation, "Information"
End If
Me.SubformA.Requery

*LinkID is a field that link main form and a subform

Anyone have an idea? Thank you in advance

 

[June7]

What does "not working" mean - error message, wrong result, nothing happens?

Remove * character.

 

[theDBguy]

Why can't you just use the query q_sum_A to populate t_cost_fix? Are you trying to store calculated values?

 

[Gasman]

I would build a string for every pk selected and use that for a select criteria.
That of course would just be for view, but not sure what you want to do.
Use an insert query with the same criteria otherwise.

 

[Pat Hartman]

I can't figure out what you are trying to do. Storing the same data in multiple places is always wrong. All you need is the FK. Then the values stay in the table that is populating the listbox's RowSource.

 

[moke123]

strsql = "INSERT INTO t_cost_fix(LinkID, CODE, VALUE, NOTE)" & _
                    "VALUES(" & Me.LinkID* & "," & ctrl.ItemData(varitem) & ")"

Note and Value are reserved words and should not be used as field names.

Are you using a multi-select listbox?

 

[MajP]

Learn about Access reserved words and symbols - Microsoft Support

support.microsoft.com

 

[nax009]

What does "not working" mean - error message, wrong result, nothing happens?

Remove * character.

the error said:

 

[nax009]

Why can't you just use the query q_sum_A to populate t_cost_fix? Are you trying to store calculated values?

yes

 

[nax009]

strsql = "INSERT INTO t_cost_fix(LinkID, CODE, VALUE, NOTE)" & _
                    "VALUES(" & Me.LinkID* & "," & ctrl.ItemData(varitem) & ")"

Note and Value are reserved words and should not be used as field names.

Are you using a multi-select listbox?

Actually, I have a difference name though.
and yes, i used a multiselect listbox

 

[theDBguy]

yes

Can you tell us the reasoning behind the decision to store these calculated values? What are they?

 

[nax009]

Can you tell us the reasoning behind the decision to store these calculated values? What are they?

I'm doing a material take-off database. The values in the list box are come from calculated queries from various jobs. So when it comes to calculate the cost i decided to let user put the taken-off value to a table for further summation because some of my users would like to input manual cost rate by themselves.

Should I creat the append query instead?

 

[June7]

Aside from the * character issue, SQL references 4 fields but have only 2 values.

And if field is text type, need apostrophe delimiters for data.