Help using VBA to update a master table from a temp table

[xanadude]

Hi All

I have two tables that are formatted identically....

Table1 = MasterTable
Table2 = TempTable

I am taking a copy of one of the entry from the master table and Copying it to the temp table

I then open a form on the TempTable that enables the user to modify the content without affecting the information in the MasterTable.

On Completion and Save I wish to take this modified content and update the MasterTable Using the Field "ID"

the tables for example look like this

ID NAME AGE DATE1 DATE2

I am guessing I need to use a

DoCmd.RunSQL UPDATE "MasterTable" WHERE "TempTable"

Type of command but not to sure on the correct syntax and as everything is the same the use of wildcards for all fields

Thanks in advance

Roger

 

[DavidAtWork]

You're on the right track, you just need to specify which fields are being updated and with what values, such as:
DoCmd.RunSQL("UPDATE MasterTable SET NAME = ' " & Me.NameField & " ',
AGE = " & Me.AgeField & ",
DATE1 = #" & Me.Date1 & "#,
DATE2 = #" & Me.Date2 & "#
WHERE MasterTableID = " & Me.TempTableID & "")

Me.xxx are the fields on your form. The syntax assumes age and ID are numbers and name is a string

David

 

[xanadude]

Thanks David much appreciated
I will give it ago

 

[xanadude]

OK Running into another glitch....

the command I am using is

DoCmd.RunSQL ("UPDATE Bowers_Core_Table SET agentName = ' " & Me.agentName & " '  WHERE ID = ' " & Me.ID & " ' ")

I am only trying one entry the agentName for the ID
The command line is being accepted but when running it pops up an error 3464
"Data type mismatch in criteria expression"

The agentName is pulled from the same dropdown combo source as the data entry form and the field in both tables are the same...

the format of the agentName is always firstname.lastname


a little stuck

thanks

 

[pr2-eugin]

ID sounds like a Number type to me, so loose the single quotes in the WHERE part..

DoCmd.RunSQL ("UPDATE Bowers_Core_Table SET agentName = '" & Me.agentName & "'  WHERE ID =" & Me.ID)

Would advice the use of CurrentDB.Execute over DoCmd.RunSQL..

Just curious, why are you battling with TempTable and UPDATE to master table?

 

[xanadude]

thanks for the help
mainly due to being new to this and don't know other ways to do these thing yet

 

[pr2-eugin]

IMHO, this seems to be a lot of hassle.. What is that you are trying to accomplish here by adding records to temp table and writing it back to main table?

I take that the code is working now..

 

[xanadude]

Thanks that worked a treat

So just looking at this

If the entry is a number I only need to use the " " (Double Quotes)
If the entry contains text i need to use the single and double quotes
If a date field i enter at #" & me.field & "#

Thanks again

 

[pr2-eugin]

If the entry is a number I only need to use the " & me.field & " (Double Quotes)
If the entry contains text i need to use the single and double quotes '" & me.field & "'
If a date field i enter at #" & me.field & "#

Yes, Yes and Yes..

Just remember double quotes and amp symbol together would be the concatenation symbols.. " &

Strings needs to be enclosed inbetween single quotes ', dates between hash symbols #, and Numbers need nothing..

 

[xanadude]

I am crreating a database the is recording data for Quality Control for agents that use written correspondence to customers.

The data entry form stores all the check listed data and scores dependant on the agents performance. (This works fine)
The ID field is auto number never to be duplicated.

This is for the editing of recorded data if an scorecard is challenged and the results to be amended.

So what I am doing is

1. I have a form that opens and requests the ID of the scored data. (ID) (OnClick Event opens step 2)
2. This is then copied to the temp database (Locally stored on the users interface) Main table is from a linked database (Using an Append Query)
3. I set a flag against this entry in the Master Table to indicate that it is being edited by somebody top stop multiple users in the same data. (Using and Update Query)
4. Open the required data into the Edit Form from the temp table.
5. Make all the changes in the temp table
6. If the data is not to be saved then exit the form and delete the content of the temp table
7 if the data is ok to save then write this back to the master table updating the live data
8 remove the In Use Flag (Using and Update Query)
9 Exit form

hope this makes sense

 

[xanadude]

Thanks guys got this all to work with a few modifications and tweaks.

Still need to understand how to get away from using a temp table and just store the selected data in memory as Im guessing this would be a more efficient way of handling it

again thanks