Cannot Find Table

C

chrisrigoni

Registered User.
Local time
Today, 11:23
Joined
Dec 11, 2009
Messages
13
Hello,
I am adding another database to an Admin database I have created to be able to control the accesses of all the databases from one central database. When adding this new database, everything is the same as the other databases of the same type, but for some reason it cannot find the table I am calling even though the table is in the same place that the other tables are held. Maybe I have been looking at it too long. I am sure that the name is the same as the table I have in the database which I am calling it, so that is not the problem. Thank you for your help! Here is the code (the line the problem is occurring in is in RED):

Code: 
Option Compare Database
Public Function ProgramMainaAdd(aItem As String) As Boolean
If IsNull(Forms!ProgramsMain!Pname2) Then
    MsgBox "Select Item to add before Clicking the ADD button"
Else
    x = ProgramMainAdd1(aItem, GetTempaUname())
    Forms!ProgramsMain.Pname1.Requery
    Forms!ProgramsMain.Pname2.Requery
    DoCmd.RepaintObject acForm, "ProgramsMain"
End If
ProgramMainaAdd = True
End Function
Public Function ProgramMainaRemove(aItem As String) As Boolean
If IsNull(Forms!ProgramsMain!Pname1) Then
    MsgBox "Select Item to remove before Clicking the REMOVE button"
Else
    x = ProgramMainRemove1(aItem, GetTempaUname())
    Forms!ProgramsMain.Pname1.Requery
    Forms!ProgramsMain.Pname2.Requery
    DoCmd.RepaintObject acForm, "ProgramsMain"
End If
ProgramMainaRemove = True
End Function
 
Public Function ProgramMainRemove1(aItem As String, emp As String) As String
Dim x As String
Dim x1 As Date
Dim TF As Boolean
Dim db As DAO.Database
Set db = CurrentDb
If aItem = "LOCS" Then
    Dim rs As DAO.Recordset
    Set rs = db.OpenRecordset("LOCSLAd", dbOpenDynaset)
    TF = False
    Do Until TF
        rs.MoveFirst
        rs.FindNext "[iUname]='" & emp & "'"
        If rs.NoMatch Then
            TF = True
        Else
            rs.Delete
        End If
    Loop
    rs.Close
    Set rs = Nothing
    Dim rs1 As DAO.Recordset
    Set rs1 = db.OpenRecordset("LOCSLAdp", dbOpenDynaset)
        TF = False
    Do Until TF
        rs1.MoveFirst
        rs1.FindNext "[Username]='" & emp & "'"
        If rs1.NoMatch Then
            TF = True
        Else
            rs1.Delete
        End If
    Loop
    rs1.Close
    Set rs1 = Nothing
    Dim rs2 As DAO.Recordset
    Set rs2 = db.OpenRecordset("LAs", dbOpenDynaset)
    rs2.MoveFirst
    rs2.FindNext "[Username]='" & emp & "' and [Program] = '" & aItem & "'"
    If Not rs2.NoMatch Then
        rs2.Delete
    End If
    rs2.Close
    Set rs2 = Nothing
End If
If aItem = "PARA" Then
    Dim prs As DAO.Recordset
    Set prs = db.OpenRecordset("PARALAd", dbOpenDynaset)
    TF = False
    Do Until TF
        prs.MoveFirst
        prs.FindNext "[iUname]='" & emp & "'"
        If prs.NoMatch Then
            TF = True
        Else
            prs.Delete
        End If
    Loop
    prs.Close
    Set prs = Nothing
    Dim prs1 As DAO.Recordset
    Set prs1 = db.OpenRecordset("PARALAdp", dbOpenDynaset)
        TF = False
    Do Until TF
        prs1.MoveFirst
        prs1.FindNext "[Username]='" & emp & "'"
        If prs1.NoMatch Then
            TF = True
        Else
            prs1.Delete
        End If
    Loop
    prs1.Close
    Set prs1 = Nothing
    Dim prs2 As DAO.Recordset
    Set prs2 = db.OpenRecordset("LAs", dbOpenDynaset)
    prs2.MoveFirst
    prs2.FindNext "[Username]='" & emp & "' and [Program] = '" & aItem & "'"
    If Not prs2.NoMatch Then
        prs2.Delete
    End If
    prs2.Close
    Set prs2 = Nothing
End If
If aItem = "CFTS" Then
    Dim crs As DAO.Recordset
    Set crs = db.OpenRecordset("CFTSLAd", dbOpenDynaset)
    TF = False
    Do Until TF
        crs.MoveFirst
        crs.FindNext "[iUname]='" & emp & "'"
        If crs.NoMatch Then
            TF = True
        Else
            crs.Delete
        End If
    Loop
    crs.Close
    Set crs = Nothing
    Dim crs1 As DAO.Recordset
    Set crs1 = db.OpenRecordset("CFTSLAdp", dbOpenDynaset)
        TF = False
    Do Until TF
        crs1.MoveFirst
        crs1.FindNext "[Username]='" & emp & "'"
        If crs1.NoMatch Then
            TF = True
        Else
            crs1.Delete
        End If
    Loop
    crs1.Close
    Set crs1 = Nothing
    Dim crs2 As DAO.Recordset
    Set crs2 = db.OpenRecordset("LAs", dbOpenDynaset)
    crs2.MoveFirst
    crs2.FindNext "[Username]='" & emp & "' and [Program] = '" & aItem & "'"
    If Not crs2.NoMatch Then
        crs2.Delete
    End If
    crs2.Close
    Set crs2 = Nothing
End If
If aItem = "HOURS" Then
    Dim hrs As DAO.Recordset
    Set hrs = db.OpenRecordset("HOURSLAd", dbOpenDynaset)
    TF = False
    Do Until TF
        hrs.MoveFirst
        hrs.FindNext "[iUname]='" & emp & "'"
        If hrs.NoMatch Then
            TF = True
        Else
            hrs.Delete
        End If
    Loop
    hrs.Close
    Set hrs = Nothing
    Dim hrs1 As DAO.Recordset
    Set hrs1 = db.OpenRecordset("HOURSLAdp", dbOpenDynaset)
        TF = False
    Do Until TF
        hrs1.MoveFirst
        hrs1.FindNext "[Username]='" & emp & "'"
        If hrs1.NoMatch Then
            TF = True
        Else
            hrs1.Delete
        End If
    Loop
    hrs1.Close
    Set hrs1 = Nothing
    Dim hrs2 As DAO.Recordset
    Set hrs2 = db.OpenRecordset("LAs", dbOpenDynaset)
    hrs2.MoveFirst
    hrs2.FindNext "[Username]='" & emp & "' and [Program] = '" & aItem & "'"
    If Not hrs2.NoMatch Then
        hrs2.Delete
    End If
    hrs2.Close
    Set hrs2 = Nothing
End If
If aItem = "MailRoom" Then
    Dim mlrm As DAO.Recordset
    [COLOR=red]Set mlrm = db.OpenRecordset("MailRoomLAd", dbOpenDynaset)[/COLOR]
    TF = False
    Do Until TF
        mlrm.MoveFirst
        mlrm.FindNext "[iUname]='" & emp & "'"
        If mlrm.NoMatch Then
            TF = True
        Else
            mlrm.Delete
        End If
    Loop
    mlrm.Close
    Set mlrm = Nothing
    Dim mlrm1 As DAO.Recordset
    Set mlrm1 = db.OpenRecordset("MailRoomLAdp", dbOpenDynaset)
        TF = False
    Do Until TF
        mlrm1.MoveFirst
        mlrm1.FindNext "[Username]='" & emp & "'"
        If mlrm1.NoMatch Then
            TF = True
        Else
            mlrm1.Delete
        End If
    Loop
    mlrm1.Close
    Set mlrm1 = Nothing
    Dim mlrm2 As DAO.Recordset
    Set mlrm2 = db.OpenRecordset("LAs", dbOpenDynaset)
    mlrm2.MoveFirst
    mlrm2.FindNext "[Username]='" & emp & "' and [Program] = '" & aItem & "'"
    If Not mlrm2.NoMatch Then
        mlrm2.Delete
    End If
    mlrm2.Close
    Set mlrm2 = Nothing
End If
ProgramMainRemove1 = "Done   --   " & Time() & "  --  Count = " & j
End Function
Public Function ProgramMainAdd1(aItem As String, emp As String) As String
Dim x As String
Dim x1 As Date
Dim db As DAO.Database
Set db = CurrentDb
If aItem = "LOCS" Then
    Dim rs As DAO.Recordset
    Set rs = db.OpenRecordset("LAs", dbOpenDynaset)
    rs.AddNew
    rs!UserName = emp
    rs!Program = aItem
    rs!Display = DLookup("[PName]", "[Programs]", "[Programs]='" & aItem & "'")
    rs.Update
    rs.Close
    Forms!ProgramsMain!Pname1 = Null
    Forms!ProgramsMain!Pname2 = Null
    Set rs = Nothing
End If
If aItem = "PARA" Then
    Dim prs As DAO.Recordset
    Set prs = db.OpenRecordset("PARALAd", dbOpenDynaset)
    prs.AddNew
    prs!iUname = emp
    prs!iAd = 3
    prs!iAd1 = 50
    prs!iFname = DLookup("[DisName]", "[Users]", "[Userid]='" & emp & "'")
    prs!iProj = aItem
    prs!iFiltera = "ALL"
    prs!iOther = "N/A"
    prs.Update
    prs.Close
    Dim prs1 As DAO.Recordset
    Set prs1 = db.OpenRecordset("PARALAdp", dbOpenDynaset)
    prs1.AddNew
    prs1!Proj = aItem
    prs1!UserName = emp
    prs1.Update
    prs1.Close
    Dim prs2 As DAO.Recordset
    Set prs2 = db.OpenRecordset("LAs", dbOpenDynaset)
    prs2.AddNew
    prs2!UserName = emp
    prs2!Program = aItem
    prs2!Display = DLookup("[PName]", "[Programs]", "[Programs]='" & aItem & "'")
    prs2.Update
    prs2.Close
    Forms!ProgramsMain!Pname1 = Null
    Forms!ProgramsMain!Pname2 = Null
    Set prs = Nothing
    Set prs1 = Nothing
    Set prs2 = Nothing
End If
If aItem = "CFTS" Then
    Dim crs As DAO.Recordset
    Set crs = db.OpenRecordset("CFTSLAd", dbOpenDynaset)
    crs.AddNew
    crs!iUname = emp
    crs!iAd = 3
    crs!iAd1 = 50
    crs!iFname = DLookup("[DisName]", "[Users]", "[Userid]='" & emp & "'")
    crs!iProj = aItem
    crs!iFiltera = "ALL"
    crs!iOther = "N/A"
    crs.Update
    crs.Close
    Dim crs1 As DAO.Recordset
    Set crs1 = db.OpenRecordset("CFTSLAdp", dbOpenDynaset)
    crs1.AddNew
    crs1!Proj = aItem
    crs1!UserName = emp
    crs1.Update
    crs1.Close
    Dim crs2 As DAO.Recordset
    Set crs2 = db.OpenRecordset("LAs", dbOpenDynaset)
    crs2.AddNew
    crs2!UserName = emp
    crs2!Program = aItem
    crs2!Display = DLookup("[PName]", "[Programs]", "[Programs]='" & aItem & "'")
    crs2.Update
    crs2.Close
    Forms!ProgramsMain!Pname1 = Null
    Forms!ProgramsMain!Pname2 = Null
    Set crs = Nothing
    Set crs1 = Nothing
    Set crs2 = Nothing
End If
If aItem = "HOURS" Then
    Dim hrs As DAO.Recordset
    Set hrs = db.OpenRecordset("HOURSLAd", dbOpenDynaset)
    hrs.AddNew
    hrs!iUname = emp
    hrs!iAd = 3
    hrs!iAd1 = 50
    hrs!iFname = DLookup("[DisName]", "[Users]", "[Userid]='" & emp & "'")
    hrs!iProj = aItem
    hrs!iFiltera = "ALL"
    hrs!iOther = "N/A"
    hrs.Update
    hrs.Close
    Dim hrs1 As DAO.Recordset
    Set hrs1 = db.OpenRecordset("HOURSLAdp", dbOpenDynaset)
    hrs1.AddNew
    hrs1!Proj = aItem
    hrs1!UserName = emp
    hrs1.Update
    hrs1.Close
    Dim hrs2 As DAO.Recordset
    Set hrs2 = db.OpenRecordset("LAs", dbOpenDynaset)
    hrs2.AddNew
    hrs2!UserName = emp
    hrs2!Program = aItem
    hrs2!Display = DLookup("[PName]", "[Programs]", "[Programs]='" & aItem & "'")
    hrs2.Update
    hrs2.Close
    Forms!ProgramsMain!Pname1 = Null
    Forms!ProgramsMain!Pname2 = Null
    Set hrs = Nothing
    Set hrs1 = Nothing
    Set hrs2 = Nothing
End If
If aItem = "MailRoom" Then
    Dim mlrm As DAO.Recordset
    Set mlrm = db.OpenRecordset("MailRoomLAd", dbOpenDynaset)
    mlrm.AddNew
    mlrm!iUname = emp
    mlrm!iAd = 3
    mlrm!iAd1 = 50
    mlrm!iFname = DLookup("[DisName]", "[Users]", "[Userid]='" & emp & "'")
    mlrm!iProj = aItem
    mlrm!iFiltera = "ALL"
    mlrm!iOther = "N/A"
    mlrm.Update
    mlrm.Close
    Dim mlrm1 As DAO.Recordset
    Set mlrm1 = db.OpenRecordset("MailRoomLAdp", dbOpenDynaset)
    mlrm1.AddNew
    mlrm1!Proj = aItem
    mlrm1!UserName = emp
    mlrm1.Update
    mlrm1.Close
    Dim mlrm2 As DAO.Recordset
    Set mlrm2 = db.OpenRecordset("LAs", dbOpenDynaset)
    mlrm2.AddNew
    mlrm2!UserName = emp
    mlrm2!Program = aItem
    mlrm2!Display = DLookup("[PName]", "[Programs]", "[Programs]='" & aItem & "'")
    mlrm2.Update
    mlrm2.Close
    Forms!ProgramsMain!Pname1 = Null
    Forms!ProgramsMain!Pname2 = Null
    Set mlrm = Nothing
    Set mlrm1 = Nothing
    Set mlrm2 = Nothing
End If
ProgramMainAdd1 = "Done   --   " & Time() & "  --  Count = " & j
End Function
 
Last edited:
Apart form the obvious the db you are looking in is the current db

Set db = CurrentDb

Then in this db you say you have both

MailRoomLAd

and

MailRoomLAdp

Are these linked tables or local?
are they queries?

Also in your the snippet

Code: 
   Do Until TF
        mlrm.MoveFirst
        mlrm.FindNext "[iUname]='" & emp & "'"
        If mlrm.NoMatch Then
            TF = True
        Else
            mlrm.Delete
        End If
    Loop

issuing a MoveFirst at each loop will result in the FindNext finding the same record over and over again, if I am not mistaken.

David
 
Linking was the issue thank you very much for your help. I found the problem. I had to link the tables on the front end of the database. I appreciate your fast response!
 
Ok got that sorted, butr what about my second query with the .MoveFirst?
 
You must log in or register to reply here.

Similar threads

R
VBA not working for saved query parameters
2
Replies
24
Views
748
RonPaii
RonPaii
amorosik
Recordset field not readable after update
Replies
18
Views
674
cheekybuddha
cheekybuddha
N
How to ensure that the current stock control is update when the data appears on the subform in access
Replies
5
Views
835
Minty
Minty
S
Solved Need help with number as text
2
Replies
23
Views
1,671
KitaYama
K
S
Recordset.Requery still showing deleted record
Replies
6
Views
654
dalski
D

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom