Updating record by code

[THWatson]

Using Access 2000

Here is a piece of code that is behind a "Save" button on a form.

Dim sql As String
Dim sql2 As String

sql = "INSERT INTO tblProjects ( CustomerID, CompletionDateActual, [b]ProjectNbr[/b], Description, Deposit, Accepted, PSTExempt, GSTExempt ) " _
& "SELECT tblInvoice.CustomerID, tblInvoice.CompletionDateActual, [b]tblInvoice.ProjectNbr[/b], tblInvoice.Description, tblInvoice.Deposit, tblInvoice.Accepted, tblInvoice.PSTExempt, tblInvoice.GSTExempt " _
& "FROM tblInvoice " _
& "WHERE tblInvoice.ProjectID Not In (Select ProjectID From tblProjects);"
DoCmd.RunSQL sql

All fields are appended except for the field in bold print, ProjectNbr.

They are the same data type (text). The value for this field is calculated from Year(Date) & "-" & CustomerID & "-" & ProjectID

Any reason this field shouldn't plug in?

Tom

 

[jrbarnett]

If you run the select part of the query (eg

SELECT tblInvoice.CustomerID, tblInvoice.CompletionDateActual, tblInvoice.ProjectNbr, tblInvoice.Description, tblInvoice.Deposit, tblInvoice.Accepted, tblInvoice.PSTExempt, tblInvoice.GSTExempt
FROM tblInvoice _
WHERE tblInvoice.ProjectID Not In (Select ProjectID From tblProjects

Does it show the data correctly as you would expect? If yes, then the problem is in the insert statement (look for mismatches on the data type and size); if its not shown correctly here, then the problem is getting your data into the tblInvoice table in the first place.

John

 

[THWatson]

John
Thanks for your reply.

I am going to have to go back and re-think how I am doing this. It's a Customers, Quote and Invoice database.

It's in moving stuff from the Quote to the Invoice process...and also allowing for a short "time and materials" job, without a Quote, to be invoiced.

The way I have it set up, I think there's some circular stuff going on and that's what is causing the problem.

Back to the drawing boards.

Tom

 

[ssatech]

ProjectNbr is a calculated value.

I don't believe this calculated value is stored in your tbl.Invoice.ProjectNbr field, hence reason it is not appended to tblProjects.ProjectNbr field.

 

[THWatson]

ssatech
Well, actually, the value is stored in the tblInvoice.ProjectNbr field. The user cannot input the value directly as the field is disabled in the form. The value is calculated as part of a "Save record" command button and so is stored in the table.

Tom

 

[ssatech]

I must be blind as I did not see how the calculated value is determined from your SQL query above.

At any rate I tried to duplicate your Tables & Fields and ran your code. I had no problem appending tbl.Invoice.ProjectNbr field value into tblProjects.ProjectNbr field. But this was only working when the data on this field is not calculated.

Otherwise I tried using a query behind your form to calculate the ProjectNbr but this data is not transferred to the actual tblInvoice.ProjectNbr. Hence data is not appended to tblProjects.ProjectNbr field.

How are you appending a calculated value from your form to a table? If you say this calculated value is is your table then it should appending to your tblProjects.ProjectNbr field.

You got me!

 

[THWatson]

ssatech
Sorry for causing confusion. And you're not blind. The ProjectNbr value is not determined in the SQL I provided. It's determined earlier.

What happens is that there is a form in which the user enters Project quotes on an electrical job - Customer information, estimated time and labour rate, and a list of materials that will be needed. The materials are on a subform.

If the Quote is accepted, there is a command button on the form which when pressed takes the data into another form from which a final Invoice is prepared. This is done by an SQL, which is an append query process. All of this is working fine.

However, I also need to allow for those instances when there is no prior quote - a short duration time and materials job only - but still needs an Invoice. This part is the fly in the ointment, because I still want to roll that stuff into the Projects table.

Tying this all together is where everything isn't working to my satisfaction, so I have to go back and think through my tables once again.

I apologize for putting you to extra effort. I didn't mean to do that. As I said in my reply to John, I have to go back to the drawing boards for a bit.

I got me too!

Tom

 

[ssatech]

Understand now, best of luck on your solution.

 

[THWatson]

Thanks.

Tom