Update ALL Lines of DataSheet

[karl009]

Hi,

I have a sub-form that is in the datasheet view, am using an Excel form to import the data after the import some of the AfterUpdate functions are being missed so I have made a button to call the AfterUpdate function.

The problem is that the button works but for only the selected line of the datasheet how can I make it update all the lines of data in the form.

The PartNumber_AfterUpdate is just looking up data for different fields and doing some calculations.

Here is the code;

Private Sub Update_DblClick(Cancel As Integer)
    Call PartNumber_AfterUpdate
End Sub

Many Thanks
Karl

 

[Trevor G]

You should look at the update query, and you can then explore if you automate it with some additional VBA code. Indicated below is a sample of how to use VBA to update a table

Function sqlParticipant2()
DoCmd.SetWarnings False
Dim sql As String
sql = "UPDATE [XIAP Stub dataset New] " 'The Table to Update
sql = sql & "SET [XIAP Stub dataset New].[Participant 2]='2'" 'The field and content to add
sql = sql & "WHERE [XIAP Stub dataset New].[Participant 2]<>''" 'The Field and the current content to change
Debug.Print sql
DoCmd.RunSQL sql
DoCmd.SetWarnings False
End Function

You should consider clarifying your question with more detail and also let us know what version of Access you are using.

 

[karl009]

Hi,

I am using Access 2007, but will mainly be run with Access 2003.

Normally the part number would be typed in, after its in the below AfterUpdate code would lookup the data required for the description and enter it.

Because the data has been imported in from Excel it has missed the AfterUpdate event would there be away to call the AfterUpdate event to update all the different line on the sub-form.

Private Sub PartNumber_AfterUpdate()
Desc = DLookup("ITEMDS", "ITEMDTL", "ITEMNO=" & "PartNumber")
End Sub

Thanks
Karl

 

[Trevor G]

Karl the sample SQL code to run an update would do that for you.

Have you explored the UPDATE QUERY and use the criteria of the subform field, if that works you can then convert the query SQL code to run from the After Update Event.

If you find it to tricky then upload a sample database with the table and forms and any query that might be linked to it, explain which table and which fields.

 

[karl009]

Hello,

I have started to look at the update query, this seems to be what am after...

However I have tried to run the dlookup and I get the message unknown.

Here is what I have;

UPDATE Lines SET Lines.[Desc] = DLookUp("MMITDS","AMBITEMDTL","MBITNO=" & "PartNumber")
WHERE (((Lines.[Our Reference])="REF00121AB"));

Is there a problem with the Dlookup, it works in the AfterUpdate Event.
Many Thanks
Karl