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.

 

[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.