After Splitting and Linking Table, VBA Error

[steve21nj]

Hi All,

On this lovely Friday just before leaving for the weekend, I began getting a run-time error, cannot be a zero-length. After about 30 minutes, I isolated the problem to the linked table named [Codes]. If I import codes to the front end from the back end, the vba behind the form works perfect. After I delete the table from the front end and re-link the table to the back end, it produces the error.

Again, this error is not produced if imported to the front end. But since the database is shared on the network, I need it linked.

Any suggestions as to why? Below is the code I am using for the form. Attached is the error message.

Option Compare Database
Function NewRequisitionNumber() As String
 
    Dim startRange_Var As Long, lastNum_Var As Long, yearValue_Var As Long
 
    On Error GoTo Err_Execute
 
    'Retrieve last number assigned for Requisition Number
    codeDesc_Var = Nz(DLookup("Code_Desc", "Codes"), 0)
    lastNum_Var = Nz(DLookup("Last_Nbr_Assigned", "Codes"), 0)
    startRange_Var = Nz(DLookup("StartingRange", "Codes"), 0)
    yearValue_Var = Nz(DLookup("YearValue", "Codes"), 0)
 
    Set DB = CurrentDb()
    Set rstsource = DB.OpenRecordset("Codes", dbOpenTable)
 
    'If no records were found, create a new Requisition Number in the Codes table
    'and set initial value to 1
    If lastNum_Var = -1 Then
        codeDesc_Var = Nz(DLookup("Code_Desc", "Codes"), 0)
        yearValue_Var = Nz(DLookup("YearValue", "Codes"), 0)
        startRange_Var = Nz(DLookup("StartingRange", "Codes"), 0)
        lastNum_Var = Nz(DLookup("Last_Nbr_Assigned", "Codes"), 0)
 
        Set DB = CurrentDb()
        Set rstsource = DB.OpenRecordset("Codes", dbOpenTable)
 
        'New Requisition Number is formatted as "LRS-12-60001", if year is 2012 for example
        LNewRequisitionNumber = codeDesc_Var & "-" & yearValue_Var & "-" & startRange_Var + lastNum_Var
    Else
        'Determine new Requisition Number
        'New Requisition Number is formatted as "LRS-12-60001", if year is 2012 for example
        LNewRequisitionNumber = codeDesc_Var & "-" & yearValue_Var & "-" & startRange_Var + lastNum_Var
        'Debug.Print (CLng(startRange_Var) + CLng(lastNum_Var))
        'Increment counter in Codes table by 1
        LUpdate = "UPDATE Codes SET Last_Nbr_Assigned = " & lastNum_Var + 1
 
        CurrentDb.Execute LUpdate, dbFailOnError
    End If
 
    NewRequisitionNumber = LNewRequisitionNumber
 
    Exit Function
 
Err_Execute:
    'An error occurred, return blank string
    NewRequisitionNumber = ""
    Call MsgBox("An error occurred while trying to determine the next Requisition Number to assign." & Chr(10) & Err.Description, vbCritical)
End Function

 

[mahenkj2]

When you compile, what it says??

 

[gemma-the-husky]

i would set a breakpoint, and step through to monitor the variables etc.

 

[MarkK]

What line causes the error?

 

[steve21nj]

I will stop in the office tomorrow to verify the line. From my memory, on the Form_Load event, it would run

Private Sub Form_Load()

NewRequisitionNumber = LNewRequisitionNumber

End Sub

or something similar. This is where the error would start.

I had a backup, non split database I used shortly before I left for the day. I tested the database that it generated the custom number, with no problem. I would compile, no issue. As soon as I split the database and open the form, it would generate the same error as my first post. Again, I would delete the linked table and import it to the front end, works perfect.....But that defeats the purpose of splitting and running a multiuser environment.

Has anyone run into the error such as this after splitting the database and linking tables?

 

[spikepl]

dbOpenTable does not go together with linked tables

 

[steve21nj]

Spikepl....thanks for the info. I researched and in fact it does not work. Based on my code from the first post, any suggestions on making it work?

 

[JANR]

Taken from Access Help files:

If you open a Recordset in a and you don't specify a type, OpenRecordset creates a table-type Recordset, if possible. If you specify a linked table or query, OpenRecordset creates a dynaset-type Recordset.

so:
Set rstsource = DB.OpenRecordset("Codes", dbOpenDynaset)

or
Set rstsource = DB.OpenRecordset("Codes")

JR

 

[steve21nj]

JANR,

Thank you, works like a charm...

Thank you to everyone else for the feedback and suggestions.