update table values with form

[dhunter]

I have a form that does a dlookup on a bunch of fields from a table. When I edit the information in the fields and save it, the form saves it as a new record. How can I make it so it only updates the field in the table instead of creating a new record?

--Newbie

 

[Scooterbug]

It's not updating the orginal values because it sounds like the form isn't bound. The Dlookup only pulls the values from the tables. Nothing more.

Your best bet would be to create a query to find the fields that you want and use a bound form to display that data in a form. This way you can update the record(s).

You could use vba to do it the way you want, but I would try the above route first.

 

[dhunter]

It's not updating the orginal values because it sounds like the form isn't bound. The Dlookup only pulls the values from the tables. Nothing more.

Your best bet would be to create a query to find the fields that you want and use a bound form to display that data in a form. This way you can update the record(s).

You could use vba to do it the way you want, but I would try the above route first.

Do you know the sql format for updating a table? I know the syntax is different from a select statement but I am not sure how it works.

Do you know what the code looks like for the acCmdSaveRecord ? It would be similar but updating.

 

[Scooterbug]

Do you know what the code looks like for the acCmdSaveRecord ? It would be similar but updating.

acCmdSaveRecord will only save the record you are on. When you do a Dlookup, you are not loading that record into the form. You are only retreiving the value for that field.

For example, say you have a bin full of blue legos (This represents the record that you currently have bound to your form). You want to add some red legos into the mix. So you tell someone (Access) to go get some based on some criteria like size. (This represents the DLookup). What Access does is goes to a separate bin, finds the particular lego you asked it to find and makes a copy. It leaves the orginial lego in it's own bin and brings you the copy. So if you change that copy, it will not change the orginial.

Now, if your form is based off a query that contains the table that has the value you are looking up, the legos from that table become part of your bin full of blue legos and can be modified.

Do you know the sql format for updating a table? I know the syntax is different from a select statement but I am not sure how it works.

As for the Sql format...the easiest way to do it is to create a new query in the query builder. You can then switch to Sql view and it will show you the correct sql syntax for that query.
If you are going to build the query in code and execute it there, remember that if you have quotes in the Sql view, you are going to have to wrap that section in quotes (I usually use chr(34))

 

[Fifty2One]

I have a form that does a dlookup on a bunch of fields from a table. When I edit the information in the fields and save it, the form saves it as a new record. How can I make it so it only updates the field in the table instead of creating a new record?

--Newbie

Use the criteria for your dllookup as the criteria for the SQL to go back to update the records. You can build this behind a button with the vb editor. To get the fields sorted for doing the update you can build the update query to work how you need and then look at the code by clicking on view in SQL. Then just write the code to the structure similar to the code below.
Just note you need the single quote for test '
ALso you need to surround date with #
and no ' or # for a numeric.

Here is a sample for using SQL to update a table
LOCATION field is updated to a hard code statement
WHOLOCATION field is updated to a Text field in adifferent open form which is used for a signin form
BRAIDFINISHWHO field is a numberic field updated from a text field on the form
BRAIDFINISHWHEN field is a date field updated to the time stamp the sql is activated
STALKER is the key field for the unique record

Dim Sql As String
Dim THEREBYWHO As String
Dim NOWITHERE As Date


NOWITHERE = Now()
THEREBYWHO = [Forms]![FrmOutOfTwister]![Combo4]

            Sql = "UPDATE tblIAMHERE SET "
            Sql = Sql & "[LOCATION] = 'DONE BRAIDING', "
            Sql = Sql & "[WHOLOCATION] = '" & THEREBYWHO & "', "
            Sql = Sql & "[BRAIDFINISHWHO] = " & me.Text6 & ", "
            Sql = Sql & "[BRAIDFINISHWHEN] = #" & Now() & "# "
            Sql = Sql & " WHERE [STALKER] = '" & me.Text8 & "' ;"
            
'Turn warning off, run SQL, turn warning back on again
DoCmd.SetWarnings False
DoCmd.RunSQL Sql, 0
DoCmd.SetWarnings True