Update Query through my Form is updating the wrong fields

[cjcobra]

So I have an update query and the "Update To" is linked to a my loaded form with multiple comboboxes. The main "Criteria" is also linked to a combobox on the same form. So when I pulldown "Project X" and change the various other comboboxes, I want them to update all fields for the "Project X". It doesn't seem to be updating consistantly. Sometimes it updates other lines. I have the criteria for the other pulldowns as "Is Not Null", otherwise it Nulls all the fields out, even if I only want to update one item. I tried to run a "Refresh" macro afterwards to clear all fields on the form to start over, but that didn't work either?

 

[cjcobra]

Well, I have it working off and on. It will say "Do you want to update 3 rows" and I say Yes, but it doesn't always update them. I guess I need some sort of refresh afterwards?

 

[CBrighton]

Not sure about the issue of it not alweays updating the correct rows, but if you are having issues with the refresh try requery instead.

 

[cjcobra]

I amaze myself sometimes. I have it working fine now. The only problem is my "Is Not Null" Criteria for all the fields. Obviously if there isn't anything in it, I want to do nothing. But is still Nulls them out?

 

[CBrighton]

Personally I wouldn't do it via a query.

I'd build a query via VBA based on the controls on the form and do something like 'db.RunSQL strQuerySQL'. I'd put this all in a command button's on click event.

Assuming your combo boxes are purely to hold the data which is being put into the new fields (i.e. none of them are selecting which fields to update) it's as simple as doing something like:

'Generic start
strQuerySQL = "UPDATE tblTableName SET "
 
'Check if cboComboBox1 is not null, if so add SQL to update that field
If not isnull(cboCombobox1) then
     strQuerySQL = strQuerySQL & "tblTableName.FieldName = '" & cboComboBox1 & "', "
End If
 
'Copy above for each combobox
 
'Remove the comma & space from the end of the SQL
strQuerySQL = left(strQuerySQL,Len(strQuerySQL)-2)
 
'Add ; to the end
strQuerySQL = strQuerySQL & ";"

This will mean that any comboboxes which are left as null will not feature in the SQL, it will be dynamically generated to only update the fields which have had a new value on the form.


If some comboboxes relate to field names the same kind of code can be used, but it will differ slightly as you need to dynamically add the field name to the SQL too.

 

[cjcobra]

Would I put this in a macro and have a button point to it?

 

[CBrighton]

The VBA would be fired via a command button, yes.

But it would be an event procedure in VBA rather than a macro.

 

[cjcobra]

ok, so it doesn't seem to be working as I thought.

I have a combobox ("SubCat") at the top for Projects, Say I select "Project A". Then I have a combobox ("OpLead") that pulls from a table. I want to update all "Project A"'s with what I select in "OpLead". I have about 10 of these, but working small at first. It's updating the table "All_Info", field "Operational Lead". I'm not sure where it should say "For all records matching "Project A", update to "OpLead". This is what I've got. Thanks for the help!

Private Sub Command19_Click()
'Generic start
strQuerySQL = "UPDATE All_Info SET "
 
'Check if cboComboBox1 is not null, if so add SQL to update that field
If Not IsNull(OpLead) Then
     strQuerySQL = strQuerySQL & "All_Info.Operational Lead = '" & OpLead & "', "
End If
 
'Copy above for each combobox
 
'Remove the comma & space from the end of the SQL
strQuerySQL = Left(strQuerySQL, Len(strQuerySQL) - 2)
 
'Add ; to the end
strQuerySQL = strQuerySQL & ";"
End Sub

 

[cjcobra]

I also tried this, which I seem to follow, but it seems to update the 5th record down each time, which of course doesn't match my SubCat combobox? I thought I had it with this one.

strQuerySQL = "UPDATE All_Info " _
        & "SET All_Info.Operational Lead = Forms![Update Form SubProject]![OpLead] " _
        & "WHERE [All_Info]![Project Subcategory] = [Forms]![Update Form SubProject]![SubCat];"