Getting a String from a table using VBA

[mlstewart]

I've got a form "AllowEntryForm" and upon loading, I want to do this:

I've got a table called "IDTable" and in that table is a field called "ID" where a string will be stored. I want to get that string in the IDTable and compare it to another string (PassCode). If they match then the form (AllowEntryForm) closes and opens up a different form (MainForm). If they do not match then the form (AllowEntryForm) opens up where they will have to enter the string that will be stored in the table.

I'm not sure how to do this but I attempted to figure it out. Currently I have "AllowEntryForm" as the form that opens at startup.

I would like to do this whole process when the database is opened but I don't know how to tell Access to do that at startup so I just tried doing it upon the loading of the form that opens at startup.


This is how I am attempting to do it:


Private Sub Form_Load()

Dim IDNum As String
Dim stMainForm As String
Dim stAllowEntryForm As String
Dim stIDTable As String
Dim PassCode As String

PassCode = "ABC123"
stMainForm = "MainForm"
stAllowEntryForm = "AllowEntryForm"
stIDTable = "IDTable"


IDNum = "SELECT [" & ID & "] From [" & stIDTable & " ]"


If IDNum <> PassCode Then
DoCmd.OpenForm stAllowEntryForm

ElseIf IDNum = PassCode Then
DoCmd.Close stAllowEntryForm
DoCmd.OpenForm stMainFormName

End If


End Sub



I am not getting any errors but my problem is even though the string in the table matches the PassCode, it is opening up the AllowEntryForm but it is supposed close it and open up a different form.

Does anyone have any suggestions?

 

[PHV]

Replace this:
IDNum = "SELECT [" & ID & "] From [" & stIDTable & " ]"
By this:
IDNum = DLookUp("ID", stIDTable)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[mlstewart]

I tried your suggestion but it still opens up the AllowEntryForm. The string that matches the PassCode is in the IDTable so it should automatically close the AllowEntryForm and Open up the MainForm.


If IDNum <> PassCode Then
DoCmd.OpenForm stAllowEntryForm

ElseIf IDNum = PassCode Then
DoCmd.Close stAllowEntryForm
DoCmd.OpenForm stMainForm

End If



Under Tools/Startup I selected the AllowEntryForm as the form to display at startup. Could this be causing the problem?

Thanks,
ML

 

[PHV]

Try this to discover if all is ok:
IDNum = DLookUp("ID", stIDTable)
MsgBox "IDNum='" & IDNum & "'"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[mlstewart]

Yes, it works fine.

 

[mlstewart]

I've played with it a little. I changed it so that the MainForm is the form that opens at startup. I took the code and put it in the On Open section for the MainForm.

I want it so that if the IDNum doesn't match the PassCode then it will open the AllowEntryForm on top of the MainForm. Currently it is opening the MainForm on top with the AllowEntryForm behind it. How can I bring the AllowEntryForm to the front?

 

[mlstewart]

I got it to work. I had to set the pop up option to Yes on the AllowEntryForm.

Thanks!!

 

[mlstewart]

Using

IDNum = DLookUp("ID", stIDTable)

when the string in the table is NULL, I get an error message saying "Run-Time error '94' Invalid Use of Null"

How can I fix this to handle null strings?

Thanks

 

[PHV]

IDNum = Nz(DLookUp("ID", stIDTable))


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244