query not checking for newly created record

[JWawrzak]

I created form called "frmInputNewRec", to automate the creation of new document numbers. Unfortunately the query attached to this form does not check for the newly created document number. How to fix the code so this will work corectly.
Below is the code for this form:

Private Sub Building_AfterUpdate()


Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset
Dim sNum As String
Dim nextnum As String
Dim stDocName As String
Dim BuildingDef As String
Dim tdf As DAO.TableDef
Dim DocNum As String
BuildingDef = Forms!frmInputNewRec!Building


Set dbs = CurrentDb
Set tdf = dbs.TableDefs("DocumentRegistry")
Set rst = tdf.OpenRecordset()
Set qdf = dbs.QueryDefs("qryLastNum")
qdf.Parameters("Forms!frmInputNewRec!Building") = Forms!frmInputNewRec!Building
Set rst = qdf.OpenRecordset()

Me.Requery

MsgBox BuildingDef
MsgBox rst!DocNum

nextnum = Val(Right(rst!DocNum, 4)) + 1

MsgBox nextnum, vbYesNoCancel

If vbYes Then


DoCmd.OpenForm "DocumentRegistry", adOpenDynamic, adLockOptimistic
With rst!DocNum

DoCmd.GoToRecord , , acNewRec

Forms!DocumentRegistry!DocNum() = (Forms!frmInputNewRec!Building & (Val(Right(rst!DocNum, 4)) + 1))
DoCmd.RefreshRecord
End With
rst.Close
Set rst = Nothing

Else:
MsgBox "Canceling Entry"
DoCmd.Close
End If


Set qdf = Nothing
Set rst = Nothing
Set dbs = Nothing

And here is the SQL for the query:

SELECT DocumentRegistry.DocNum
FROM DocumentRegistry RIGHT JOIN Activity_Registry ON DocumentRegistry.DocNum = Activity_Registry.DocNum
WHERE (((DocumentRegistry.DocNum) Like [Forms]![frmInputNewRec]![Building] & "-####") AND (([Activity_Registry]![DocNum])=[DocumentRegistry]![DocNum]))
ORDER BY DocumentRegistry.DocNum DESC , Activity_Registry.DocNum DESC

Thank for any suggestion that will help me solve this problem.

Jozef A. Wawrzak

[aytee111]

Is "new document numbers" a new record in a table? Maybe your sub is in the wrong place, try putting it in the form "AfterUpdate".

[ssanfu]

Welcome to the forum, Jozef.

I am confused about what you are trying to do. You have a form "frmInputNewRec", but in the code you open another form??

Is this a multi-used dB?
If yes, how many users a one time?
Is the db split?

I would probably use the form before uodate event if there are other fields that need to have data entered.
If it is only enter "building" (name?), then using the control after update event would be OK.

You could post your dB with just a couple of example records.......


I did revise your code.. this is what I came up with:

Private Sub Building_AfterUpdate()

    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset

    Dim iNextNum As Integer
    Dim sBuildingDef As String
    Dim sSQL As String

    Set dbs = CurrentDb

    sBuildingDef = Forms!frmInputNewRec!Building

    sSQL = "SELECT TOP 1 DocumentRegistry.DocNum"
    sSQL = sSQL & " FROM DocumentRegistry RIGHT JOIN Activity_Registry ON DocumentRegistry.DocNum = Activity_Registry.DocNum"
    sSQL = sSQL = " WHERE DocumentRegistry.DocNum Like " & sBuildingDef & "*;"
    sSQL = sSQL = " ORDER BY DocumentRegistry.DocNum DESC , Activity_Registry.DocNum DESC"
    '    Debug.Print sSQL

    Set rst = dbs.OpenRecordset(sSQL)
    If Not rst.BOF And Not rst.EOF Then
        MsgBox sBuildingDef
        MsgBox rst!DocNum

        iNextNum = Val(Right(rst!DocNum, 4)) + 1

        MsgBox iNextNum, vbYesNoCancel

        If vbYes Then
            sSQL = "INSERT INTO DocumentRegistry ( DocNum )"
            sSQL = sSQL & " VALUES ('" & (sBuildingDef & iNextNum) & "');"
            '    Debug.Print sSQL
            dbs.Execute sSQL, dbFailOnError
        End If

    End If

    rst.Close
    Set rst = Nothing
    Set dbs = Nothing

End Sub

Not saying it will work, but ............

[JWawrzak]

The form "frmInputNewRecord" should create a new record in the table called "Document Registry" where DocNum field is indexed - no duplicates. The table "DocumentRegistry is linked with another yet table "Activity_Registry" where duplicates for DocNum are OK.