Use an update query to copy data from a form to a new table

[DrPat]

I am trying to use an update query to copy 4 fields from the current record on a form to a different table. A command button will execute the query, as well as other commands, via a macro.

"Edit Patient" is the form which the user uses to navigate the main table
"Print Template" is the table that I would like to copy the data to

Here's what I have so far (which updates absolutely nothing):

UPDATE [Print Template] SET [Print Template].[First Name] = [Forms]![Edit Patient]![First Name], [Print Template].[Last Name] = [Forms]![Edit Patient]![Last Name], [Print Template].[D/A] = [Forms]![Edit Patient]![D/A], [Print Template].DOB = [Forms]![Edit Patient]![DOB];


No criteria are necessary. If the command button is clicked, I want the update to occur.

Where am I going wrong? (novice user here, so be gentle)

 

[AccessMSSQL]

How are you running this query (executing it)? Are you calling it from a button on your form and executing it through VBA code? Also, what is Print Template? Is this a temporary table or do you have unique records in it - the reason I ask is because I don't see a WHERE statement in your update query. You have to be updating a specific record in the table for that to work. Also, does Print Template have any records in it? If you want to insert new records you need to use an INSERT statement, which would be an Append Query.

 

[AccessMSSQL]

Oops..sorry I read your post a bit more and see it's coming from command button...so do you have any code executing behind the command button?

 

[DrPat]

Yes. There is code to execute a mail merge from the newly updated table (a table with one record). But that comes after the update query.

Some background info, if it helps: I used some code which runs mail merge from Access, the problem was that it merges the whole table and I only want one record (the current record on the form).

My solution was to create a table dedicated to the merge, then make a query to update that dedicated table to the current record, and then execute the code for the merge.

I hope this helps (helps you help me, that is)

Thanks,
Pat

 

[DrPat]

<More to the story>

The command button will launch a macro which will run the query, then execute the mail merge code.

I don't want an append query because I only want one record at a time in the table.

I did try several WHERE statements. i.e., WHERE Forms![Edit Patient].[Last Name] is not null. The table still did not update

The purpose of the whole thing: We sometimes have 8-10 pages of patient forms to be filled out. I'm trying to save my staff time and reduce errors by creating a button to print the forms with much of the repetitive info already on it.

They're too long and difficult to format to use an access report. MS Word/merge is really the only way to go.

 

[JHB]

Do you have one record in the table when you try to update the table?
If not, you can't use at update query!
If yes, why not delete the record in it and the use a insert query?

 

[DrPat]

Genius....I put in a dummy record to start and it updates. There is one problem and one strange phenomenon, however.

The problem is that the DOB field does not update.

The strange phenomenon is that the warning tells me that I am about to update 140 rows (the number of records in the main table). The print template table has ony 1 row and the query shouldn't be looking at the main table.

again, the code is below:

UPDATE [Print Template], [Patient Data Table] SET [Print Template].[First Name] = [Forms]![Edit Patient]![First Name], [Print Template].[Last Name] = [Forms]![Edit Patient]![Last Name], [Print Template].[D/A] = [Forms]![Edit Patient]![D/A], [Print Template].DOB = [Forms]![Edit Patient]![DOB];

 

[JHB]

...The problem is that the DOB field does not update. ...
See what the value of [Forms]![Edit Patient]![DOB] is just before you update.

MsgBox([Forms]![Edit Patient]![DOB])

...that the warning tells me that I am about to update 140 rows (the number of ...
Yeah but you've also put both table name in:

UPDATE [Print Template], [Patient Data Table] SET [Print Template].[First Name] = [Forms]![Edit Patient]![First Name], [Print Template].[Last Name] = [Forms]![Edit Patient]![Last Name], [Print Template].[D/A] = [Forms]![Edit Patient]![D/A], [Print Template].DOB = [Forms]![Edit Patient]![DOB];

 

[DrPat]

Thanks. Removing the other table stopped the query from updating that table (go figure).


I changed the command button to run the VBA code you provided, but it returned the following message:
Run-time error '438':
Object doesn't support this property or method.


I'm testing this on a different computer running access 2000, is that an issue with VBA?

 

[JHB]

Hmm - where did you place it? Show your sub.
Never heard that MsgBox given an error code, it is a bacis thing in VBA.

Sub YourSub() 
...
...
  MsgBox([Forms]![Edit Patient]![DOB])
...
 ..
End Sub

 

[DrPat]

Here it is, cut and pasted.

Private Sub Command172_Click()
MsgBox ([Forms]![Edit Patient]![DOB])
End Sub

 

[JHB]

You should place it where you are doing the update, to se if [Forms]![Edit Patient]![DOB] has a value or not because you wrote "The problem is that the DOB field does not update."

 

[DrPat]

OK. I didn't want to get bogged down in the message box issue, so I tried another method.

I created a test update query to update [Print Template].[last name] to "DOB is null" if [Forms]![Edit patient]![DOB] is null

So the problem is that the update query sees the DOB field as null. Now I am certain that it is not null because I am running this query from a command button on the form where I can clearly see a date in the field.

My first suspicion was a mis-spelling, but now that's been ruled out and there were no errror messages of missing fields.

Any ideas?

 

[DrPat]

OK nevermind. I found the problem. (I was using the name of the field in the table instead of the name of the text box. For some strange reason, I didn't name it DOB).

Thanks for your help and insight JHB!
/Pat

 

[JHB]

You're welcome, good you found the problem.