auto populate table fields (1 Viewer)

[isladogs]

Yes using CurrentDb.Execute in VBA as I suggested. It only affects that line of code.
Alternatively you can use DoCmd.SetWarnings False before the query in VBA and DoCmd.SetWarnings True afterwards...but that will suppress all messages including error messages.

 

[ElPerson]

Yes using CurrentDb.Execute in VBA as I suggested. It only affects that line of code.
Alternatively you can use DoCmd.SetWarnings False before the query in VBA and DoCmd.SetWarnings True afterwards...but that will suppress all messages including error messages.

where to put the VBA code to affect this query?

 

[isladogs]

Where/when do you want it to run?
Possibly on a form load event or a button click event?

 

[ElPerson]

Where/when do you want it to run?
Possibly on a form load event or a button click event?

i made the form of revenue to run the query as an after update, so after each insertion of the payment the sales table updated accordingly, But i keep seeing the warning message.

where do you suggest to add the VBA code in order to suppress these messages?

 

[isladogs]

Are you running this as a saved query from code or running the SQL statement posted earlier?

 

[theDBguy]

i made the form of revenue to run the query as an after update, so after each insertion of the payment the sales table updated accordingly, But i keep seeing the warning message.

where do you suggest to add the VBA code in order to suppress these messages?

How exactly did you make the form to "run" the query? That's where you would put the additional code.

 

[ElPerson]

Are you running this as a saved query from code or running the SQL statement posted earlier?

i used the SQL statement posted earlier

 

[ElPerson]

How exactly did you make the form to "run" the query? That's where you would put the additional code.

the query opened by macro, and the code is VBA

how to add both of them??

 

[theDBguy]

the query opened by macro, and the code is VBA

how to add both of them??

Hi. Did you see my post earlier? You can just modify your macro, instead of combining your macro and VBA.

 

[isladogs]

I'm sorry but that makes no sense at all.
Please show a screenshot and/or post the full code used

 

[ElPerson]

I'm sorry but that makes no sense at all.
Please show a screenshot and/or post the full code used

This is the code used to make the job done

UPDATE Sales INNER JOIN Revenues ON (Sales.[Customer]=Revenues.[Customer]) AND (Sales.[Date]=Revenues.[Invioce Date]) SET Sales.[Paid On] = Revenues.[Payment Date]
WHERE Sales.[Paid On] Is Null;

 

[ElPerson]

Hi. Did you see my post earlier? You can just modify your macro, instead of combining your macro and VBA.

i did tried to SetWarnings to No But still the messages showing

 

[isladogs]

I suggest the following

Private Sub YourControlName_AfterUpdate()

Dim strSql as string

Strsql = "your code from the last post here"
CurrentDb.Execute strSql, dbFailOnError

Note that there is no macro involved using that approach

 

[ElPerson]

I suggest the following

Private Sub YourControlName_AfterUpdate()

Dim strSql as string

Strsql = "your code from the last post here"
CurrentDb.Execute strSql, dbFailOnError

Note that there is no macro involved using that approach

sorry
would you please tell me where to put this CODE??
should i add in place my code?

 

[isladogs]

Add it as an event procedure of whatever control you are updating.

 

[ElPerson]

Add it as an event procedure of whatever control you are updating.

is this code right

Private Sub Form_AfterUpdate()
Private Sub YourControlName_AfterUpdate()

Dim strSql As String

strSql = "UPDATE Sales INNER JOIN Revenues ON (Sales.[Customer]=Revenues.[Customer]) AND (Sales.[Date]=Revenues.[Invioce Date]) SET Sales.[Paid On] = Revenues.[Payment Date]"
WHERE Sales.[Paid On] Is Null;"
CurrentDb.Execute strSql, dbFailOnError
End Sub

as ive got error message from visual basic window

 

[theDBguy]

i did tried to SetWarnings to No But still the messages showing

Hi. I'm sure you'll get it to work eventually, but could you please post a screenshot of your macro showing how you tried to use the SetWarnings action? I'm just curious...

 

[isladogs]

Umm...no.
Scrap the Form_AfterUpdate line
Replace the 'yourcontrolname' in the next line with the actual name of the control being updated

Note that I'm giving you instructions for an event procedure.
DBG is advising you about a macro solution.
Do one or other but not both

 

[ElPerson]

Umm...no.
Scrap the Form_AfterUpdate line
Replace the 'yourcontrolname' in the next line with the actual name of the control being updated

Note that I'm giving you instructions for an event procedure.
DBG is advising you about a macro solution.
Do one or other but not both

what do you mean by control?? the table??
and i know that i should choose either one

 

[ElPerson]

Hi. I'm sure you'll get it to work eventually, but could you please post a screenshot of your macro showing how you tried to use the SetWarnings action? I'm just curious...

i deleted it right now and working on the VBA way

i'll reply later with a screen shot

Thak you