Update query Help

[Ciara]

Hoping if somebody could possibly help!

I have a problem with update queries and the lack of a result. I'm trying to take data from two tables and force the results into a separate table( as all the information in the table is a duplication of data elsewhere, but it needs to be keep together as an invoice). I've tried a update query through the GUI trying to force the data from the tables directly I've also tried an SQL query (below) to force from a form, but the message just reads you are about to update 0 rows. The update query also doesn't provide any results. I wonder if it's a problems with the relationships, or maybe just me!! OH Heavens above will some body please help me I think It will drive me insane.

Private Sub Form_Close()
DoCmd.RunSQL "Update [Invoice_Line] " & _
"set Invoice_No=Forms![Invoice].[Invoice_Line_Query subform]![Invoice_No]," & _
"Invoice_Line_Date=Forms![Invoice].[Invoice_Line_Query subform]![Invoice_Line_Date]," & _
"Venue_ID=Forms![Invoice].[Invoice_Line_Query subform]![Venue_ID]" & _
"Where Invoice_No=Forms![Invoice].[Invoice_Line_Query subform]![Invoice_No];"
DoCmd.SetWarnings True
End Sub


Thank you

Ciara



p.s. (The Technical user may be a bit of a lie, you must excuse my lack of access knowledge, I've only been playing with it for 3 weeks.)

 

[DougP]

Zero records means you have no matches or no records that Access found to process.
Take it one step at a time.
First make a copy of the table for backup.
Then
Create a new query and leave the other one alone for a moment.
Don't do any "updating" in this test just try to find record(s) that match your criteria. Start by having just one or two fields then add the rest and another table if necessary.
Once you can see one or more records then add the Update feature.
Once that's working then copy and paste the SQL view code into your VBA code.

After further examination of your query in the QBE grid it appears that you are referencing the Inoveice number in both the subform and the main form.
try removing the one in the subform casue it should be syncing on its own.

DougP, MCP
dposton@universal1.com

Ask me how Bar-codes can help you be more productive.

 

[Quehay]

Ciara,

Sounds like you're trying to do a make-table query--update only changes values in the existing table. Try choosing the make-table option and then have a go. Make sure both tables have a unique index.

Are you making this table so that you'll have a flat file with all real values rather than indexes, thinking that having relational tables is too volatile?

Also, why are you pulling values from forms rather than the tables themselves--the form values SQL looks like a genuine update query but if you've pulled existing values into a form why not just use standard form updating of the recordset. Please post again for any further questions.

 

[Ciara]

Thank you very much for the advice I ended up using an insert query from the gui, which works .

Thank you again!!

Ciara