Updating fields in local access table -afterupdate event on form

[KevinSlater]

Hi,

I have an access form named “SCREEN-DATA-LOAD_SHEET” which has some code on the after update event behind a filed named orde num (second field from left of form) which I’d like to update /insert some data into fields in the local access table named: “DATA-LOAD_SHEET”. It gets the data from an SQL view named “dbo_View_LoadSheetV2”
The code is as below:
Private Sub OrderNum_AfterUpdate()
Let [DATA-LOAD_SHEET.Account] = [dbo_View_LoadSheetV2.CardCode]
Let [DATA-LOAD_SHEET.Shipto] = [dbo_View_LoadSheetV2.ShiptoCode]
End Sub

However when I try to use the form by selecting sales order in the drop down entry type and then entering an order number – I’m using 45768 as an example I either get an error message saying run time error 2465 - Microsoft access cant find the field '|' referred to in your expression or it just inserts the sales order number into the DATA-LOAD_SHEET table but no other information.

Any help would be great, I’ve attached a sample version of the access database with just a few records. Thanks

 

[pr2-eugin]

Ther is no Form in the mdb filr that you have attached..

 

[KevinSlater]

The form named "SCREEN-DATA-LOAD_SHEET" i just opend the file I uploaded and could see it ok, can you have another look please?, i can re-upload again

 

[pr2-eugin]

Nope, i did that.. I could not see it.. there is only one Query called.. "SCREEN-DATA-LOAD_SHEET"

 

[KevinSlater]

Ok ive re-uploaded, hopefully this one works ok.? I can see the form so not sure why it want visible.

 

[pr2-eugin]

The error occurs because, you have not included the field in the Query.. Go to the recordSource property of the form and include DATA-LOAD_SHEET.Shipto in the Query.. Save it.. Now the error does not occur.. I do not know hoe your whole form works.. but it is not allowing me to save the record..

 

[KevinSlater]

ok ive included Shipto in the query and dont see the error now however the data does not get updated in the table DATA-LOAD_SHEET.
the account field doesnt get updated either

Any ideas why?

 

[pr2-eugin]

That is because, you are not trying to update the fields on the table but just assigning it to variables..

I was playing around with your form.. so based on that you have to requery the record source so that it is bound directly to the main table.. what you should do is to lookup the values.. since they are bound to the fields.. on exit they will update automatically..something like..

Let CardCode = DLookup("CardCode", "dbo_View_LoadSheetV2", "DocNum=" & OrderNum & "")
Let ShipToCode = DLookup("ShiptoCode", "dbo_View_LoadSheetV2", "DocNum=" & OrderNum & "")

So, in summary, change the record source.. make the values lookup..

 

[KevinSlater]

Thanks a lot, changed as suggested and now working fine