Add New Record & Generate Control No for New Record

[aquabp]

I have a receiving database that when keying in an unknown item the code pulls up a form through the notinlist procedure. I have two issues.

1. The Code I have pulls up the form and is suppose to put the new value in the lookup table record then allow me to finish putting in the rest of the info needed for that record. It doesn't. I think it is because it was originally had two sub forms inside. I have changed this to a single form connected to the master table.

Not worried about fixing 1. right now.

2. The update form does not generate a new value.

The lookup form was a stand alone form to add in lots of new part numbers. On its own it would use a combo box to look to see if that Item (ISort) was in the table. There was a sub form linked to the combo box. If the item was not in the table the cursor would move to the sub form and generate a new control item number (GSort) and add that field along with the ISort Field to the master table then let me finish up the rest of the record. It does not do that now that I am using it in the NotinList procedure.

Key Process:
1. Key in details of a purchase receipt. Item No or ISort from store is keyed to database.
2. If it is in the master table it pulls up the description etc. If not it pulls up a form to enter the new record to the master table.
3. The form should have the ISort Value in the update form. It does not. Not worried about fixing this right now.
4. It should create a GSort Number that is unique. Currently it is not doing that. See Code below


2. Combo box use to be part of the update query. I have taken the combo box out of the query since it is no longer on the form.
3. I Key the ISort Number directly onto the new line in the form and enter. The error message says I must fill in the GSort Value.
4. When I look at the master table it has the new GSort Value but there is now ISort value even though I keyed it in.

Here is the code I am using.

Private Sub GSort_Enter()
'Code Purpose
                'Item SetUp.
                'Verify Item is in lookup table if not add it and also create a new GSort then move cursor to Description Field.
                'Refresh form so that inventory table is up-to-date.
                'Once ISort is populated look at the GSort field. if it is not Null tab to vendor field.
                'if  the GSort field is null then update field with the max GSort No + 1 Tab to Description field.
                'if GSort is not null then move to Cost field.
                
    'Dim StrSQL As String
    
    If IsNull(Me.GSort) Then                                    ' ISort keyed and GSort is null Then
    
        DoCmd.SetWarnings False
        'StrSQL = "INSERT INTO 350_IMaster ( GSort, ISort ) SELECT [350_IF01Q02].NRec, [Forms]![350_ISetUpF01].[Date2] AS i FROM 350_IF01Q02;"
        'StrSQL is the query in the DoCmd Statement below. Query does not connect with combo box Date2 since it is not there anymore. 
        DoCmd.OpenQuery "350_IF02Q01"
        Forms![350_ISetUpF01].Refresh
        DoCmd.GoToControl "Description"
        DoCmd.SetWarnings True
    
Proc_50IF02MC01_Exit:
    Exit Sub
Proc_50IF02MC01_Err:
    MsgBox Error$
    Resume Proc_50IF02MC01_Exit



 End If                                                    ' End the IsNull test


                                                            'Future If GSort is not null move Cost Field
End Sub

I hope this clarifies what I am looking for.
Any help would be greatly appreciated!

[aquabp]

Important Note I think: The query uses the ISort Number from the combo box on the original code as noted on SQL statement. On the new code the update is only the Gsort because there is no combo box on the new form. The ISort number has been keyed to start the new record.

[ItsMe]

I am not following the Workflow the User has to follow. If you are trying to use a popup form to append a record to a table, because an item isn't available in a combobox control, you can use the NotInList event of the combo to open the PopUp form. Here is an example of the not in list event. You can transfer the text entered into the combo by the user to the popup form. I prefer to not include this text in the popup in case there is a typo in the original value.
https://www.accessforums.net/program...tml#post292507

[aquabp]

Thanks for responding. I edited the main post. Hope it makes sense now.