Populate continuous form with temporary data from table

[Aubs010]

Sorry if the subject is a bit misleading, but it *is* what I'm striving for!

I have a table and all the records within are accessible to all users.

On a form I have a couple of search criteria that can be set which then Runs a query to gather all the records that match.

I then stupidly programatically copied the records to a 'temp' table, and didn't think about any other user doing the same procedure that would also over-write the 'temp' table, causing random problems!

Is there a way to create a temporary table in memory
or
Would it be good enough to try and create a unique table when the form loads
i.e.
create random number
look if table with that number as a name exists
if it does --> create another number
loop back
if it doesn't --> set that number as a global constant for the current user and delete the table once done


Thanks all for any help

Aubs

 

[PWise]

Option Compare Database
Option Explicit

 Private Type GUID
    Data1 As Long
    Data2 As Integer
    Data3 As Integer
    Data4(7) As Byte
End Type

Private Declare Function CoCreateGuid Lib "OLE32.DLL" (pGuid As GUID) As Long

Private Function GetGUID() As String
Dim udtGUID As GUID
If (CoCreateGuid(udtGUID) = 0) Then
    GetGUID = _
    String(8 - Len(Hex$(udtGUID.Data1)), "0") & _
    Hex$(udtGUID.Data1) & _
    String(4 - Len(Hex$(udtGUID.Data2)), "0") & _
    Hex$(udtGUID.Data2) & _
    String(4 - Len(Hex$(udtGUID.Data3)), "0") & _
    Hex$(udtGUID.Data3) & _
    IIf((udtGUID.Data4(0) < &H10), "0", "") & _
    Hex$(udtGUID.Data4(0)) & _
    IIf((udtGUID.Data4(1) < &H10), "0", "") & _
    Hex$(udtGUID.Data4(1)) & _
    IIf((udtGUID.Data4(2) < &H10), "0", "") & _
    Hex$(udtGUID.Data4(2)) & _
    IIf((udtGUID.Data4(3) < &H10), "0", "") & _
    Hex$(udtGUID.Data4(3)) & _
    IIf((udtGUID.Data4(4) < &H10), "0", "") & _
    Hex$(udtGUID.Data4(4)) & _
    IIf((udtGUID.Data4(5) < &H10), "0", "") & _
    Hex$(udtGUID.Data4(5)) & _
    IIf((udtGUID.Data4(6) < &H10), "0", "") & _
    Hex$(udtGUID.Data4(6)) & _
    IIf((udtGUID.Data4(7) < &H10), "0", "") & _
    Hex$(udtGUID.Data4(7))
End If
End Function

What I do in cases like this is to create a GUID On form open And when I Append the records to the temp table I add A field with the guid and add to the query

"where .... and guidfield=" Me.txtGuid

 

[Aubs010]

Great idea, pwise, many thanks for that

Aubs

 

[PHV]

Didn't each user have a local copy of the FrontEnd and thus have their own temporary tables ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Aubs010]

PHV - Sorry for the late reply, for some reason I've not been getting email notifications even though it shows 'Turn off E-mail Notification.' (implying it is already on!).

No, users do not have their own local front end.

What I have done now, is create one table that stores all users search results. but with one additional field - UserID.

Thus, where ever a user logs in from (logs in to a computer and accesses the front-end), their previous search is retrieved automatically. This was done because they may look for a drawing in the office and then when on site log in to a computer there to retrieve the same drawing.

I have also provided an administrative function for clearing the user-search table.


Aubs