Using a public variable as a Record Source within a table

[Cyd44]

I am trying to open a form to show all records which relate to the Logged in User only for a given date.

My form record source is linked to the table and I want to set a condition where the Table myID matches the current user Login Id. I have the following SQL Select statement.

SELECT tblRoomsBooking.BookLocation, tblRoomsBooking.BookStartDate, tblRoomsBooking.BookTime, tblRoomsBooking.BookEndTime, tblRoomsBooking.BookName, tblRoomsBooking.Faculty, tblRoomsBooking.myID
FROM tblRoomsBooking
WHERE (((tblRoomsBooking.BookStartDate)=[frmBookings].[BookStartDate]) AND ((tblRoomsBooking.myID)=[basMyEmpId].[lngMyEmpID]))
ORDER BY tblRoomsBooking.BookLocation, tblRoomsBooking.BookStartDate;

The lngMyEmpID is captured at logon and declared as a public variable and is used elswhere without a problem. However, I am getting a paremeter box appearing when I open the form which wants me to enter the ID. I simply want the form to select records based upon the login ID matched to the myID field within the table.

My form does not appear to recognise the public variable and I dont understand why.

[NeoPa]

It won't. Your public variable is in a VBA module and the Record Source is essentially SQL. Jet, the SQL Engine, has no knowledge of your modules, and no dot (.) syntax for referencing the object structure.

To use it you could design a Public Function procedure which returns that value and call the function within your SQL.

[Cyd44]

Thanks NeoPa,
Thats clear although I am not sure how to do it though. Will have to do some digging.

[NeoPa]

It's straightforward enough Cyd :

Module = [basMyEmpId]

Public lngMyEmpID As Long

Public Function GetEmpID() As Long
    GetEmpID = lngMyEmpID
End Sub

That way, your SQL would be something like :

SELECT   [BookLocation]
       , [BookStartDate]
       , [BookTime]
       , [BookEndTime]
       , [BookName]
       , [Faculty]
       , [myID]
FROM     [tblRoomsBooking]
WHERE    (([BookStartDate] = [Forms]![frmBookings]![BookStartDate])
  AND    ([myID] = GetEmpID()))
ORDER BY [BookLocation]
       , [BookStartDate]

[Cyd44]

Many thanks NeoPa. I will save this as an example because I have found a work around that works fine for what I want to do.

Your example however will be very useful to me for other things.

Thanks again

[NeoPa]

Cyd44:
I have found a work around that works fine for what I want to do.

That's great. The solution was only posted as it matches the question. It's not an approach I would use myself except in extremis. I expect your solution is more appropriate for your requirements than this - which is essentially a kludge, but works (and, of course, it never hurts to know that it is available).