VBA help needed to update table based on list box multi selected records

[krjoshi]

Cross Post: http://www.access-programmers.co.uk/...d.php?t=271760


Hi Folks,

I have database with an userform called “AssignWP”,
combobox called “WPDevBy”, listbox called “List352” (Multi select) and table
called “Justified”.
I am
trying to update one field “WPDevelopedBy” of the table as combobox value based
on list box multi selected records
I have attached the Database and picture of what I am trying to
accomplish.

Thanks in
advance,

Joshi

[June7]

Use code that loops through the listbox and builds criteria string that can be used with IN condition of query.

Example of looping listbox and building the string: http://allenbrowne.com/ser-50.html

To use the string in code behind the subform:

CurrentDB.Execute "UPDATE tablename SET WPDevelopedBy='" & Parent.combobox & "' WHERE " & strWHERE

Another approach loops through the subform RecordsetClone object.

And even another approach would physically move to each record and set the value.

[ssanfu]

I modified your code...

I added "Option Explicit" to all of the modules. BTW, you have an error in the "Toggle3_Click()" code. Do a "compact".... you'll see it.

I had to change the query "ForList" (added the field "ID") and the list box (List357 - added "ID") column count and widths. And, of course, the "List357" code.


Good luck with your project.

[krjoshi]

Hi Steve,
Thank you so much for your time and help. Your code isworking excellent...1000 thanks. If you don't mind can you please help me tofix one more issue (I have tried with your current code, but I am unable to fixit). It's similar to your current code does, I have another form called"UpdateWP" in that I have placed 8 nos. text boxes called txt1 ,txt2, txt3... to txt8 now I want to update the table called"Justified" with these text boxes values to relevant fields based onlist box selected items. Can you please advise or help me to fix this. I haveattached the Database and picture of what I am trying to accomplish.

And kindly advise, is there any way to restrict the user to enter only numerical values in the textboxes (for txt1 to txt7 = only allow to enter numbers 1 to 10 and for txt8e enter numbers 1 to 30)?
Thank you so much for your time help once again.
Joshi

[June7]

Can restrict user input with ValidationRule property: >0 And <11 Or Is Null
Then ValidationText could be: Enter a value between 1 and 10.

[krjoshi]

Hi June7 and ssanfu, thanks for you help and support.
Can you help me to update the "Justified" table with text boxes values? I have tried with Steve's current code ( It's similar to Steve's current code). I have another form called "UpdateWP" in that I have placed 8 nos. text boxes called txt1 ,txt2, txt3... to txt8 now I want to update the table called"Justified" with these text boxes values to relevant fields based onlist box selected items. Can you please advise or help me to fix this. I have attached the Database and picture in my my previous reply of what I am trying to accomplish.

Thanks in advance,
Joshi

[June7]

Appears you still have not run Debug > Compile - the errors Steve mentioned are still there.

Looks like need to change Me.WPDevBy to Me.WPDevBy1

The Developer combobox has developer name in RowSource, not developer ID. Need the ID if you want to save ID into Justified.

How can WPDevelopedBy have a value of 10? The developer IDs only go up to 8.

Why are these fields in Justified set as text if you want to save number values?

[ssanfu]

You are/were missing a field in the table Justified - I added the field "DraftIsolationCert". There is code for the update button now

June explained to you how to limit the text boxes.

----------------------
Suggestions:

*The top two lines in EVERY module should be:

Option Compare Database
Option Explicit

*Do NOT use spaces, punctuation or special characters in object names. You have a table named "All Jobs" (has a space)
There are fields that have "(", "$", "-", "/" and spaces in the names. Bad practice.
You don't need field names like "Driver (Reason for Doing the Job)". Lots of typing.... maybe could use "JobDriver" or "ReasonForJob". There is a field in the table definition for a description of the field if necessary.
field names of "Insulation" and "Crane" (to me) are not descriptive enough.
The thing is that no one but the/a programmer should see the design view of tables/queries. There are label controls to add more descriptive names on the forms.

*Rename control names. Which is easier to know the purpose: "Toggle332" or "cmdClose" when reading code?

*You might read up on event properties. You have a combo box with the click event and the change event. I would use only the after update event.

[krjoshi]

Hi Steve,

Thank you so much your code is working excellent..!!! One last question for this post, if I change the table's filed Data Type to "Number" (see the picture) the code is getting error message (see the picture). how I can fix that? please advise.

Thank you so much for your time and help once again...

Joshi

[ssanfu]

Remove the single quotes in strSQL:

        strSQL = "UPDATE Justified SET Justified.Drawings = " & Me.txt1 & ",  Justified.SiteReview = " & Me.txt2 & ","
        strSQL = strSQL & " Justified.BasicLiftingPlan = " & Me.txt3 & ", Justified.DraftIsolationCert = " & Me.txt4 & ","
        strSQL = strSQL & " Justified.Materials = " & Me.txt5 & ", Justified.Scaffold_Insulation = " & Me.txt6 & ","
        strSQL = strSQL & " Justified.Phtographs = " & Me.txt7 & ", Justified.WP_DetailSteps = " & Me.txt8
        strSQL = strSQL & " WHERE Justified.ID = " & ctl.Column(0, varItem) & ";"

[June7]

Remove the apostrophe delimiters from the SQL statement.

Text fields require apostrophe delimiters and date/time fields use #, numbers don't need any.

These fields will still work as text type as long as values don't exceed 9. That is because sorting and filtering would be by alpha rules. 15 would sort before 2. Would need placeholder 0: 02 would come before 15. This can be handled but if the fields are number, there is no sort issue.

[krjoshi]

Steve / June7,

I have removed the single quotes from the code.. But I am getting new error message as attached picture

[ssanfu]

Did you copy the code from post # 10??
It does not error when I run the code...


Did you add a line

Debug.print strSQL

and set a breakpoint to see what is displayed in the debugger immediate window?
See: http://www.cpearson.com/excel/debug.htm

[krjoshi]

Hi Steve,
i am so sorry, that was my mistake. Your code is working excellent. Thank you so much Steve and June7 for your time and help. The thread is solved now.