Update Subform after an append Query is Run

[aquabp]

I am trying to speed up my item master update process. Currently if the item number is not in the table I have to look up the new GSort number and copy and past it into the table. Ya I know! w

Table Name: 350_ITest
Vendor Item Field: ISort
Company ITem Field: GSort
Unbound Lookup field on Form: Date2
Append Query: 350_ITest Query: It takes the value in Date2 and finds the Max Gsort+1 then appends to 350_Test table.
Master form:350_ItemF01
Sub Form 1: 350_ItemF02 - Connected to Date2
Sub Form 2: 350_ItemF03 - Connected to ISort field in Sub Form 1

So When I hit enter after keying in the ISort in Date2 the cursor moves to Sub Form 1 where the data in Date2 is in the Isort field. I have put the code in the GSort gotfocus event on Sub Form 1 so when I tab to the GSort field the code goes.

This is the code. I am better with Queries and Macros so I struggle with VBA. If there is a quicker way to do this in VBA I am all ears. I'll figure it out.

Private Sub GSort_GotFocus()


If IsNull(Me.GSort) Then ' ISort keyed and GSort is null Then

DoCmd.SetWarnings False
DoCmd.OpenQuery "350_ITest Query" 'Append ISort and GSort to Table.
DoCmd.RunCommand acCmdRefreshPage 'Hopped it would update Sub Form 1. Tried me.Refesh did not work either.
DoCmd.GoToControl "Description" 'Move to next update field to key in product description.
DoCmd.SetWarnings True


End If

End Sub

Currently, the code runs and moves cursor to Description field but does not show GSort. When I try to move off of the record it says
I cannot because there is now GSort Value. When I close the form after many error messages. The New ISort and GSort numbers are there.
Anyone have any ideas that could help me out?
Thanks in Advance!!

[Homegrownandy]

Where did you put "me.refresh"?
what is a "Gsort number"?
Are all the "Gsort numbers" in the database already?
What is the query looking for to find this value? how does it work?

[aquabp]

It is not pretty but it works now. "me.refresh is in the 3rd line below. Actually refreshing the parent form.
The GSort Number is a company item number as apposed to the ISort number which is the vendor's item number.
in this case this particular GSort is being added and is new to the table because we have a new product or service.
The query is taking the max Gsort number in the table and adding one to it. This is a two step query. The second step appends to the table after grabbing the Isort number from the lookup unbound field in the main form. I show it below. I would love to learn how this would look in VBA. I played with DMax +1 but did not know how to identify it and did not understand how to use update in VBA.

I wish I new VBA better but I added it to the GSort field before update event the original post I had it in get focus.

If IsNull(Me.GSort) Then ' ISort keyed and GSort is null Then
DoCmd.SetWarnings False
DoCmd.OpenQuery "350_IF02Q01" 'Finds MaxGSort and adds 1 to give a new GSort
Forms![350_ISetUpF01].Refresh 'This refreshs the form and shows the new record in the form.
DoCmd.GoToControl "Description" 'Moves cursor to the Description field so that info can be keyed.
DoCmd.SetWarnings True

Proc_50IF02MC01_Exit:
Exit Sub
Proc_50IF02MC01_Err:
MsgBox Error$
Resume Proc_50IF02MC01_Exit


End If ' End the IsNull test

Thanks for any advice to improve this and make it more controlled. It has not shown any issues but the week is young!

[Homegrownandy]

I don't feel like I helped at all but it's okay mate haha.