Changing RecordSource Based on Password

[Abbos]

Hi,

I'm fairly new to VBA programming although not new to programming in general.

I think my problem is with my syntax but would appriate any help.

Private Sub Command7_Click()

    Dim strInput As String
    Dim strMsg As String
    Dim stDocName As String
    Dim stLinkCriteria As String
    
    strMsg = "Please enter password to proceed."
    strInput = InputBox(Prompt:=strMsg, title:="Password Prompt")
    If strInput = "gotit" Then 'password is correct
        stDocName = "Arts"
        Forms_Arts.RecordSource = ArtsQuery2
        DoCmd.OpenForm stDocName, , , stLinkCriteria
    Else 'password is incorrect
        MsgBox "Incorrect Password!", vbCritical, "Password Error"
        Exit Sub
    End If
    
End Sub

I am trying to get it so that when the user enters the correct password it will change the RecordSource of the Arts form to use ArtsQuery2 and then open.

Thanks,

 

[raskew]

Hi -

Try these changes:

stDocName = "Arts"
Forms_Arts.RecordSource = ArtsQuery2
stLinkCriteria = "ArtsQuery2"
DoCmd.OpenForm stDocName, , stLinkCriteria

HTH - Bob

 

[Abbos]

Hi Bob,

A little better but when I use your code below I get the error "The action or code is invalid because the form isn't bound to a table or query." which in turn points me to

DoCmd.OpenForm stDocName, , stLinkCriteria

 

[Abbos]

After removing the quotes from the following line of code:

stLinkCriteria = "ArtsQuery2"

I got a bit further. Now it seems to be running the query and opening the form but I dont think it is applying the query as the RecordSource as the form is showing all records in the DB however the query cuts it down to only show 3 records which the form is not doing. I have checked the query and it does run and return the correct results.

 

[billyr]

If the stored query already produces the records you want, why bother with strlinkcriteria at all. Just set the form recordsource to the stored query.
stDocName = "Arts"
Forms_Arts.RecordSource = ArtsQuery2
DoCmd.OpenForm stDocName

 

[Abbos]

Thanks Billy. I changed the code but now I have the issue whereby as part of the ArtsQuery2 which I am using as the RecordSource the query has two fields which use the "Like" which relate to the form that opens. The problem I have is that when it goes to open the form using the code you posted I get two prompts which I have to click ok twice then it opens the form.

Is there a different order I can do things in, i.e. open the form as hidden, apply the RecordSource as ArtsQuery2 then display the form to the user? I have no idea if that is possible but I just trying to think of ways around it.

Many Thanks,

 

[raskew]

Hi -

Why not just create an additional form, bound to the desired query? Then it's a simple matter to switch between the forms, based on the circumstances.

Bob

 

[billyr]

It sounds like 2 fields in the query are getting criteria from the form? Here is a snippit of code that dynamically sets the filter for a form in one of my applications:

Select Case Me.frmFilter
Case 1 'Show ONLY active resident records
Me.Filter = "act = true"
Me.FilterOn = True
Case 2 'Show ALL resident records
Me.FilterOn = False
End Select
Me.Requery

(Me.frmFilter is an option group control on the form.) You could do something similar with your form.

 

[Abbos]

Thanks for the replies. I think I am going to have to go down the route of creating seperate forms and queries.

Billy your idea is good but I am trying to achieve the following:

A user opens the application and gets a splash screen. They select Main database and are prompted for a password. Depending on the password they enter the Main database then might only show all Ownerships (a field) which are set to Company for example. It could be that a user enters a higher level password and All Ownerships are shown.

The other issue is that I also have a search facility on the form which is pretty much a FilterByForm allowing the user to deepen their searches.

 

[Abbos]

Too much of a pain in the end. Thanks for your help anyways.

I ended up creating all the forms and queries and on the splash screen where the button is for the main database it prompts the user for a password. Depending on the password eneter it will open the macthing form.