Update a Table from a Form 1

[illini]

I have a table; each record has a unique record ID (Autonumber). I have a form which is not tied directly to a table which is used to manipulate the table date.

To update a current record to the table, a Cmd_button is pressed. This takes the info (including unique record ID) off of the form and then uses an update query (with a criteria based on the record ID).

How can I update the table with a brand new record (with no current record ID) using the same process? Since the update query keys in on a record ID, it's not working. I can't seem to get the update query to key in on a new entry.

-illini

 

[Junior1544]

here is some logic that i'm thinking of that may help get you going...

if the record id is left blank then
run an add query.
else
run the update query
end if

that's not in vba, because i don't know exacly how you are running things, but hope fully it will help...

--James
junior1544@jmjpc.net
Life is change. To deny change is to deny life.

 

[BlackKnight]

Hi,

If I understand you correctly you have a table (tableA), in which, you are updating records in it. You are using asn unbound form to update theses records. So, soemhow you are laoding the form with a record including an autonumber. The user clicks on command button to run an update query for the given record.

So, you want to be able to do this and add a new record to the table by clicking on the command button.

In the On Click event of the command button do a check on the textbox that is holding your autonumber for the current record to see if it is null. If it is null then the user is adding a new record. Check if the required info is filled out and then run an append query instead of an update query like this:

On_Click

if not isnull(txtRecID) then
'run update query
else
'check if info is filled in for new record.
'run append query.
'INSERT INTO tableA (Fld1,Fld2) VALUES ("' _
& txt1 & "' , '" & txt2 & "')"
end if

Have a good one!
BK

 

[Junior1544]

Thanks, i just had a minute befor to write an answer and put in the logic i was thinking... thanks for explaining it better...

--James junior1544@jmjpc.net
Life is change. To deny change is to deny life.

 

[illini]

BlackKnight,

I understand the steps you've outlined. However, when I try to add the new record using an append query, the query duplicates all of the existing records with the information of the new record. How can I specify a new record in the ID field of the query's criteria?

-illini

 

[BlackKnight]

Hi,

If I have 3 recs in a table, I load the unbound form, how does the user select a record to be updated?

Have a good one!
BK

 

[Junior1544]

I'm geting the sneeking feeling that this is a bound form... and it's bound to a select query...

--James junior1544@jmjpc.net
Life is change. To deny change is to deny life.

 

[BlackKnight]

Hi Junior1544,

<Jeopardy music> My guess would be an unbound form with a combobox filled with the record ids and in the click event a find is done using a recordset to fill in the textboxees including the record id textbox??

OR

the user is entering the record id into the textbox which would be extremely strange but plausible. <g>

Have a good one!
BK

 

[illini]

I discovered that my append query was tied to the table to be updated with the new record. Therefore, it was taking all of the existing records and duplicating them with the new record's data.

I was able to re-create the append query using the data from the form. It worked just as you anticipated.

Thanks for the help.

-illini