updating multiple fields of a table from a listbox

[wshs]

i have a listbox('lstBud') from form "SelectBudget"
Record Source: tbl_VRegister

quick description of what this form do.
its opened from another form, and filters users id and provides a list of their budgets. User can select a budgetline from the list box and when they click ok, the budget is transferred (table updates).

One problem. I need those columns in the list box (the one that user selects/highlights) to be captured into a table.
ie. abid, buddesc, etc.
how can i do that?
(tbl_fsource.bfsix = foreign key / tbl_VRegister.bfsix = primary key

here's source for "lstbud" listbox..

SELECT tbl_fsource.abid, tbl_Fsource.buddesc, tbl_Fsource.revnum, tbl_Fsource.revname, tbl_Fsource.appro, tbl_Fsource.fcast, tbl_Fsource.balan, tbl_Fsource.bfsix FROM tbl_Fsource WHERE (((tbl_Fsource.StaffID)=Forms!frm_selectBudget!cboStaffID)) ORDER BY tbl_Fsource.buddesc;

 

[ItIsHardToProgram]

Do you need the user to be able to select only one or should he be able to select multiple budgets?

 

[Remou]

Perhaps something like:

    If (List0.MultiSelect) Then
        For Each varItem In List0.ItemsSelected
            For i = 0 To List0.ColumnCount
                Debug.Print List0.Column(i, varItem)
            Next i
        Next varItem
    Else
        For i = 0 To List0.ColumnCount - 1
            Debug.Print List0.Column(i, varItem)
        Next
    End If

 

[wshs]

they can only select one item. im sorry but
i can't follow your codes completely..
whats it doing?

 

[ItIsHardToProgram]

His code prints out every single item selected, if it is only 1 item you only need to BOUND your form to a column in your table... for example.

you have your table
tbl_VRegister and you have your colum that is Selectbudget

so in your listbox source you should have tbl_VRegister.Selectbudget, otherwize, use the assistant to the listbox and Just select which one.


I suggest a combo box it is easyer to use for the users.


If you want to show a description of the budget you can enter more then one column, either way, the only thing to do is bound your listbox.....

 

[wshs]

i can't bound it to one column since listbox consists of multiple columns. and i also need to capture them as well

ie.SELECT tbl_fsource.abid, tbl_Fsource.buddesc, tbl_Fsource.revnum, tbl_Fsource.revname, tbl_Fsource.appro, tbl_Fsource.fcast, tbl_Fsource.balan, tbl_Fsource.bfsix FROM tbl_Fsource WHERE (((tbl_Fsource.StaffID)=Forms!frm_selectBudget!cboStaffID)) ORDER BY tbl_Fsource.buddesc;

VRegister also contains "abid", "buddesc", "revnum", etc. i need to update vregister table. making sense?

 

[Remou]

Sorry, I was not thinking. What the code does is list each column from the row selected, and with a mistake, at that. But your listbox is based on a query, so perhaps:

strSQL="INSERT INTO tblToAppendTo " _
& "( abid, buddesc, revnum, revname, appro, " _ 
& "fcast, balan, bfsix ) " _
& "SELECT tbl_fsource.abid, tbl_Fsource.buddesc, " _ 
& "tbl_Fsource.revnum, tbl_Fsource.revname, " _
& "tbl_Fsource.appro, tbl_Fsource.fcast, " _
& "tbl_Fsource.balan, tbl_Fsource.bfsix " _
& "FROM tbl_Fsource " _
& "WHERE tbl_Fsource.bfsix = " & Me.List0.Column(7, List0.ItemsSelected)
DoCmd.RunSQL strSQL

 

[wshs]

was i suppose to put that in the record source of the list box?

 

[Remou]

You said
One problem. I need those columns in the list box (the one that user selects/highlights) to be captured into a table.
ie. abid, buddesc, etc.
how can i do that?
You also said that the list box was built on:
[tt]SELECT tbl_fsource.abid, tbl_Fsource.buddesc, tbl_Fsource.revnum, tbl_Fsource.revname, tbl_Fsource.appro, tbl_Fsource.fcast, tbl_Fsource.balan, tbl_Fsource.bfsix FROM tbl_Fsource WHERE (((tbl_Fsource.StaffID)=Forms!frm_selectBudget!cboStaffID)) ORDER BY tbl_Fsource.buddesc;[/tt]

What the above query is supposed to do is to take the data as it appears in the listbox and append it to another table, which I have called tblToAppendTo, based on the item selected in a list box, which I have called List0. From you post, I thought you wanted to do this.

 

[wshs]

yea.. that sql was the source property of the list box.

strSQL="INSERT INTO tblToAppendTo " _
& "( abid, buddesc, revnum, revname, appro, " _
& "fcast, balan, bfsix ) " _
& "SELECT tbl_fsource.abid, tbl_Fsource.buddesc, " _
& "tbl_Fsource.revnum, tbl_Fsource.revname, " _
& "tbl_Fsource.appro, tbl_Fsource.fcast, " _
& "tbl_Fsource.balan, tbl_Fsource.bfsix " _
& "FROM tbl_Fsource " _
& "WHERE tbl_Fsource.bfsix = " & Me.List0.Column(7, List0.ItemsSelected)
DoCmd.RunSQL strSQL

nothing shows up on my listbox after i change it to this...

 

[Remou]

The idea is to run the sql to update the table that you want to update with the details from the list box, not to use the sql as a recordsource for your listbox. You said you wished to use the fields selected to update a table, the sql I suggested should do this. In a test database, try putting the sql in the After Update event of a similar listbox, changing tblToAppendTo to the name of the table that you want to be captured into a table, and see if the result is captured into a table in the way that you said you wanted.

 

[wshs]

thank you for clearing that up for me.

list0 = name of the list box right?

strSQL = "INSERT INTO tbl_VRegister " _
& "( abid, buddesc, revnum, revname, appro, " _
& "bfsix ) " _
& "SELECT tbl_fsource.abid, tbl_Fsource.buddesc, " _
& "tbl_Fsource.revnum, tbl_Fsource.revname, " _
& "tbl_Fsource.appro, tbl_Fsource.bfsix " _
& "FROM tbl_Fsource " _
& "WHERE tbl_Fsource.bfsix = " & Me.LstBud.Column(7, LstBud.ItemsSelected)
DoCmd.RunSQL strSQL

this is what i have. i don't get any errors but its not updating anything for some reason. did i do something wrong?

its in afterupdate event of lstbud

 

[wshs]

im getting variable not defined now..

Private Sub lstbud_AfterUpdate()

strsql = "INSERT INTO tbl_VRegister " _
& "( abid, buddesc, revnum, revname, appro, " _
& "bfsix ) " _
& "SELECT tbl_fsource.abid, tbl_Fsource.buddesc, " _
& "tbl_Fsource.revnum, tbl_Fsource.revname, " _
& "tbl_Fsource.appro, tbl_Fsource.bfsix " _
& "FROM tbl_Fsource " _
& "WHERE tbl_Fsource.bfsix = " & Me.LstBud.Column(7, LstBud.ItemsSelected)
DoCmd.RunSQL strsql

End Sub

 

[ItIsHardToProgram]

wshs look at Remous post and read it carefully

The idea is to run the sql to [highlight]update the table that you want to update with the details from the list box[/highlight], not to use the sql as a recordsource for your listbox. You said you wished to use the fields selected to update a table, the sql I suggested should do this. In a test database, try putting the sql in the After Update event of a similar listbox, changing tblToAppendTo to the name of the table that you want to be captured into a table, and see if the result is captured into a table in the way that you said you wanted

if tbl_VRegister exists it will not work, but I don't see the link with your error.

NONE the less. Remous code INSERTS INTO a new table....... I hope you saw it that way....

VRegister also contains "abid", "buddesc", "revnum", etc. i need to update vregister table. making sense?

That is why I bothered the comment... if that table needs to be updated, you can't "create" a new table with the same name either way.....

 

[wshs]

well i didn't want to create a new table...

just wanted to UPDATE tbl_Vregister...

 

[lars7]

Hi,
Have you tried something like this.


Private Sub List1_AfterUpdate()

CurrentDb.Execute "UPDATE yourtable SET yourfield = '" & Me!List1 & "' WHERE staffid = '" & Forms!yourmainform!!Text1 & "'"
CurrentDb.Execute "UPDATE yourtable SET yourfield2 = '" & Me!List1.Column(0) & "' WHERE staffid = '" & Forms!yourmainform!!Text1 & "'"

End Sub

You will need to change the names to suit yourself and add more if you have more fields in your listbox.

 

[wshs]

thx for ur input lars7

can you make sure im doing this right tho.. for some reason i can't get it to work.

yourtable = table i would like to update(tbl_VRegister)
yourfield = field from the table i would like to update(abID, buddesc, etc)
list1 = name of the list box(lstbud)
WHERE staffID(field from tbl_VRegister)
mainform = form which consist the listbox(frm_SelectBudget)
text1 = (textbox name bound to StaffID)

is this correct? im not getting any errors or stops but it seems like its not updating anything on tbl_VRegister..
what am i doing wrong?

 

[lars7]

Hi there,
You could try this first and see if it works, remember to post what you try so everyone can see it.

CurrentDb.Execute "UPDATE tbl_VRegister SET abID = '" & Me!lstbud & "' WHERE staffid = '" & Forms!frm_SelectBudget!text1 & "'"

 

[wshs]

i will give that a try when i get the chance but
for now.. i build and hid textboxes and bounded them.
ie. me.txtbox = me.lstbud.column(o)
txtbox (bound to VRegister.field)

thx