Trouble with Autonumber for a global variable (1 Viewer)

Good Morning All,

I am having some issues with Autonumber working with a global variable that I set...

My database stores information for Companys - each company has its own unique reference (currently Autonumber).
I have a global variable 'ICompanyRef' which uses this autonumber - I use this alot throughout my code. e.g.

Function BSaveDiaryAction(liContactRef As Integer, lsActionType As String, lsReason As String, lsPassTo As String, lsPriority As String, lsDueDate As String) As Boolean
Dim lsSQL As String
On Error GoTo Oops
lsSQL = ""
lsSQL = lsSQL & " INSERT INTO Tbl_Diary (CompanyRef, ContactRef, ActionType, Reason, PassTo, PassFrom, Priority, DueDate, Complete, CompletionDate) "
lsSQL = lsSQL & " VALUES (" & ICompanyRef & ", " & liContactRef & ", '" & lsActionType & "', '" & lsReason & "', '" & lsPassTo & "', '" & SUser & "', '" & lsPriority & "', '" & lsDueDate & "', " & False & ", '" & lsDueDate & "')"
CurrentDb.Execute lsSQL

BSaveDiaryAction = True

Exit Function

Oops:
MsgBox Err.Description
End Function

This has been working perfectly up to now as I have mainly been testing my work using exisiting companies. The problem I now face as the database has grown and become more complex is that when I am trying to add a new company the 'ICompanyRef' variable is not being set early enough.

I am currently getting the previous companies reference OR Null if no others have been previously picked.

I have put a breakpoint in my code to try and see whats happening... the thing I cannot understand is that although the new autonumber has been saved in both the table and the query (which the company form runs from) it cannot be picked up in the global variable.

I have tried refreshing the variable (cannot do that), I have tried a DLookup to get the companyref from the query (if gives me the wrong one)...

Can anyone shed any light on how to use autonumbers with a variable like this when creating new records???

I hope I have made some sense in this, I am still quite new with it all and dont think I explain things very well - please ask any questions if you need more information...

Many Thanks in anticipation...

Why do you need the global variable?
Why not have the CompanyRef or CompanyId in the Company Table be an autonumber?

If you are storing info about something associated with a company in the tblDiary, why not store the unique identifier from the Company Table in tblDiary as FK?

I'm not seeing why you have to manage the autonumber -- in my view that's Access' job.