Add a generated value to the underlying table field of a form using VBA

[aquabp]

I have a form that is opened when a field value in an underlying table is not found.
The table is called 350_IMaster. It has 7 fields that need to be updated when a new record is added.
Here are the 3 important fields in the table:
ISort (Short Text) this field is not found in a master form and causes this item setup window to open.


GSort (Long Int) this field is the primary field for the table
Description (Short Text) Describes the item and the field I want to move to automatically.

Note: When the form opens a new record is added and the ISort is keyed in. I hit the enter key to move to the GSort Field.
If I have the code in “On Enter” I get an error that I need to enter a GSort Value. If I have the code in “BeforeUpdate” and I have the line goto description in “AfterUpdate” The cursor goes to Description but no GSort Value shows up. When I then close the form I get that same error as above


Currently the value is not being added to the underlying table. Here is the code that I am trying to adapt. It was found online.

Dim NRec As Long
Dim db As Database
Dim rstIMaster As Recordset
 
 
    Set db = CurrentDb
    Set rstIMaster = db.OpenRecordset("350_IMaster")
 
     If IsNull(Me.GSort) Then
      ' Find the highest value in the column "GSort"
         NRec = DMax("[GSort]", "350_IMaster") + 1
         rstIMaster.Edit
         rstIMaster("GSort").Value = NRec
         Me.Refresh
         'DoCmd.GoToControl "Description"
         rstIMaster.Update
End If

Currently this is a single user program. PS I tried it with out the edit and update lines. It asked for them even though the record is a new record.
Thanks in advance for any help with this problem!

[lzook88]

Aquabp,

You may want to try and post the database file on here. That way we can see how it is set up and test out any ideas we have for helping you.

Thanks,
Z

To provide db: Make copy, remove confidential data, run compact & repair, zip w/Windows Compression if over 500KB (2MB zip allowed), attach to post. Attachment Manager is below the Advanced post editor window, click Go Advanced below the Quick Reply window.
If any suggestion resolves your issue, please use the Thread Tools dropdown above first post and mark as Solved!

[aquabp]

RecTest.zip

500_ISetUpF01 has the code I cannot get to work. It is in the GSort Field.

The 350_SetUp Form is a stand alone form that generates new GSort Numbers. Used when we know we are going to have a lot of new part numbers to add. Works great and is what I would like the VB code to do for the 500_ISetUpF01 form.

400_ReceiptF01 is the receiving form. I left one receipt in the file. Us the drop down to access it. First sub Form is receipt summary the second is detail
There is one line in the detail column for the receipt I left. Go to the second line and key in any number like 10 or 150 or something. The 500_SetUpF01 form pops up. This is how the form is being used.

Thanks for taking the time to look at this!

[lzook88]

Okay I have fixed your problem to get the GSORT to pass over for you.

Private Sub GSort_Enter()
Dim rs As DAO.Recordset

If IsNull(Me.GSort) Then                                    ' ISort keyed and GSort is null Then
    
    DoCmd.SetWarnings False
    'finds the NRec for the GSORT
    Set rs = CurrentDb.OpenRecordset("SELECT NRec FROM 350_IF01Q02")
    
    'Places the above NRec into the GSort Control
    Me.GSort = rs!NRec

    me.descirption.getfocus

end if
end sub

^^^ This will get you your generated value in the form when you press enter or tab. After you press enter or tab it will automatically jump to the description. To help out in the process - mark your GSORT text field from Enable to Disabled that way it can not be edited.

And leave your 500_ISetUpF01 as acdialog.. This works fine with it.

---

This will get you the ISort number from the main form to the add form.
Place the following code in the Default Value of the ISort on the 500_ISetUpF01 form

=[Forms]![400_ReceiptF01]![400_ReceiptF03].[Form]![ISort].[Text]

Z

[aquabp]

Thanks Z!! Everything works great! I got an error message at, "me.descirption.getfocus" that said method not found. I switched it to DoCmd.GoToControl "Description". Just wondering why getfocus could not be found. Using Home 365 access 2016.

Thank you for fixing this!! I just tried keying receipts and it cut my time in half!!!

aquabp

[lzook88]

It is setfocus not getfocus

My bad

[aquabp]

That works to!! Thanks again!!