sqlstr in a loop

AccessWillKill

Registered User.
Local time
Today, 02:54
Joined
Oct 2, 2010
Messages
50
I've been given a theory to work with over this weekend in office 2003

i have a form with alot of fields. Its been a pain. its crashed the form etc etc

basically all the field names in the form are the same as the table and what i was wondering if rather than having them all in a massive SQL string that would update the table with the relevent field information (three fields get inserted into the table on form load), is it possible to create a loop using an array to update the table, field by field, using the array like so:

sqlstr = Update tablename (tablename.fieldnamefromarray1 = formname.fieldnamefromarray1 ) Where (tablename.pkfield = formnamepkfield.value)

So basically where it says fieldnamefromarray1 the sql string would utilise the names from the array rather than taking the field names from the form and table (which would be the same names the array holds anyway) to insert into the table.

i'm not sure the sql string would like it too much but its not something i've ever attempted until this weekend.

A) is it possible
B) could i get some help with this as i go along
 
How many fields are we talking about?

Is the form not bound to the table anyway?
 
no it is a blank form for people to enter details in. It is a snapshot of the table rather than a dynaset.

i'm talking alot. The problem is that whenever the sql string runs with all the fields going at the same time it seems to either throw out an error or in the worst case crash the database which stops the form from being useable.

i'm having to create a whole new database but keep alot of the old bits. Infact i'm told to not touch these bits at all. so they're could be something in that and my code causing the problem but i'll never know.
 
Can you provide a snippet of the SQL that is being used to update the table?
 
i cannot. Mainly because i've left the version i was editing at work quite stupidly. I can assure you there was no syntax errors, because it got to the stage where i was adding field values in one at a time checking them for errors one by one. At one point a comma caused a bit of trouble before the Where clause in the string

i can give an example of the kind of string i've used in the pass if it helps, a bit of a smaller scale version of what i was doing in this, but i dunno what good it will be
 
As David already asked, How many fields are involved?And how many records?

If this is just updating some records in a table, you don't - necessarily - have to use a form. You could have data in a .csv file file for example.

If you're changing values based on a percentage or function, then there may be other options.

We can't really help until we understand what it is you are trying to achieve.
 
basically i was presented with the database and told to edit a form so it is in a snapshot. The form itself is unbound to control sources. The fieldnames hold the same names as forms from a table but it is unbound. Some fields reference different tables using record sources but not the one the created record is updating data to.

what i've been tasked to do is to when the user clicks a create an entry button on the switchboard the page will load up. use an sqlstr to insert a few fields into the database table, then put them in the relevant form fields on the form. These fields are the mandatory fields. Such as the date the record was created. who created the record and set the record to being still to complete or closed. in the case of pressing the button obviously its set 'to log' because users have more information to fill out after the form has loaded.

All this side of it is done. However. The form then needs to (when the user clicks to save the record) update the record created from when the user clicked 'create record' with all the new information that is entered on the form to the record.

The idea was to basically use an sqlstr to update the relevant record created when the user clicked 'create record' with the new information then prompt the user to say the information was saved and then close the form. However for some reason (i believe it is something to do with the coding my predecessor has created) it does not work. I attempted this and it got to one or two fields and corrupted the form. or at the very least threw out an error message.

So i was set about this task. Basically i must create an array. In that array will be all the values of the form and the table (remember both the form fields and the table fields are named the same)

The loop (i'm thinking a for loop but have struggled) will cycle through each of the fields in the array and use an SQLStr to use the array to update the table fields with the form fields one by one Rather than the original way of doing it all at the same time.

This is the way i've been set to do it. I'm put under pressure to keep the way things work in terms of user interaction with the database as similar as possible. Which means using a form. I mean the tables are not really linked to each other. it isn't really setup like a proper database. its more one main table where most the data gets inserted into. and other tables for some of the combobox values and such
 
oh. the form has 50 fields with apparently more wanting to be used if the use of a tab function is authorised for part of the form.
 
Absolutely this is possible... and not very difficult.

Code:
Dim fieldname As String
Dim strSQL As String
fieldname = ""
 
For i = 0 To 50
fieldname = fieldname & ", " & fieldname(i)
if i = 50 then strSQL = "SELECT " & fieldname & " FROM Table;"
next i

All you have to do is create a loop that will constantly add itself to itself, changing only the number that corresponds to the field. Then copy and paste from there, strait into an SQL statement.

If you want to update the fields, assuming that the field names and control names are named similarly, you just have to add this to your sql statement.

eg:

Code:
Dim fieldname As String
Dim strSQL As String
fieldname = ""
 
For i = 0 To 50
fieldname = fieldname & ", " & fieldname(i)
if i = 50 then strSQL = "UPDATE Table SET '" & fieldname & "' WHERE '" & fieldname & "' =  '" & controlname & "';"
next i

Loops... are amazing.
 
Last edited:
yeah i didn't think the SQL string would like the use of an array name for some reason.

I'll give it a go let you know how i get on.
 
You could update each one, having the docmd run every single i count, but I would rather have the loop concatenate the fields and control values before hand. It just seems cleaner that way. (And.. your screen won't blink a million times while it updates the form)

Please let me know how it works out for you. I would really like to see a final product on this.
 
One of the big problems you are going to encounter is different control types.

Numbers do not need any encapsulating
Dates need #'s
Text needs quotes or Chr(34)


It seems to me that this code only needs to written once and yes, it can be labourious, but once done you do not have to worry about it. To me hard code it and be done with it.
 
this is what i've got... when i have managed to get it to run it hasn't updated anything

Code:
Dim fieldname(50) As String
Dim strSQL As String
Dim NameOfField As Variant
 fieldname(0) = "CallHandler"
For i = 0 To 50
NameOfField = fieldname(i)
Debug.Print NameOfField
If i = 0 Then strSQL = "UPDATE contacts SET  contacts.NameOfField = '" & Forms!contactsedit.NameOfField.Value & "' WHERE (((contacts.ContactID)  =  '" & Forms!contactsedit.ContactID.Value & "'));"
Debug.Print strSQL
Next i
the debugs are just to see whats coming out and the fields(0).

I must be missing something
 
Last edited:
oh i've only put one variable in cause i didn't want to spend ages putting them all in to find it didn't work
 
i have woken up this morning and i have managed to get it to work with one field. i'm not going to attempt it with all the fields to see how i get on.
 
In your loop, the IF statement needs to be changed to 50 (if i = 50)... because you want the query to be finalized when all 50 fields are in the given variable. That might be why it isn't updating anything. Keep hammering away... we are going to get this. lol
 
it works right across... bit of a development of my own code and what was provided... this is how i've done it (i'm sure there are way better ways to do this)

Code:
Private Sub Save_Click()
Dim fieldname(48) As String
Dim strSQL As String
Dim NameOfField As Variant

DoCmd.SetWarnings False
'just fill this part in with all your array
 fieldname(0) = "CallTaker"
 

 
For i = 0 To 48
NameOfField = fieldname(i)
Debug.Print NameOfField
strSQL = "UPDATE Contacts SET contacts." & NameOfField & "  = Forms!contactsedit." & NameOfField & ".Value  WHERE contacts.contactID = " & ContactID & ";"
Debug.Print strSQL
DoCmd.RunSQL strSQL
Next i
    MsgBox ("log created and saved")
    DoCmd.Close , contactsedit
End Sub
 
This adopted technique is totally irregular! what you are doing is performing 48 table updates to the same record.

It does not take into consideration the data type of each field or the contents of the field as to whether it has a value in it or not.

This approach is quite simply doomed for failure. There is no reason why you cannot simply write on block of sql to perorm a single table update.

Baffling to say the least.:eek:
 

Users who are viewing this thread

Back
Top Bottom