Full Version: Combo Box and List Box
UtterAccess Forums > Microsoft® Access > Access Forms
LiLGaL
Hello All,
I am totally new to all of this, so please tell me if I'm doing anything wrong. My question is, Is it possible to base the results in a list box in a form on what is selected in a combo box in a previsious form? This is my situation. I have a form (frmSELECT_REGION). It has a combo box (cboREGION). The user selects the region from the combo box, and then enters the regions password (txtPassword). They are both unbound. When the user clicks the GO button and they password is correct, they are taken to another form (frmENTER_LEAK_FORM). This form has a list box(List0) which has two colums, the communities in the specified region and the region and a field (REGION) and these are all based on a query. The REGION field, so far, doesn't do anything. I want this form to be filtered, to where, it only displays the communities specified in the previous form.
Below is my code thus far. Can this be done? I thank you in advance for your help.
CODE
  
Private Sub cmdLogin_Click()
    Dim stDocName As String
    Dim stLinkCriteria As String
    stDocName = "frmENTER_LEAK_FORM"
    
    stLinkCriteria = "[REGION]=" & "'" & Me![cboREGION] & "'"
        
'Check to see if data is entered into the UserName combo box
If IsNull(Me.cboREGION) Or Me.cboREGION = "" Then
MsgBox "You must enter a Region.", vbOKOnly, "Required Data"
Me.cboREGION.SetFocus
Exit Sub
End If
'Check to see if data is entered into the password box
If IsNull(Me.txtPassword) Or Me.txtPassword = "" Then
MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
Me.txtPassword.SetFocus
Exit Sub
End If
'Check value of password in tblRegions to see if this matches value chose in combo box
If Me.txtPassword.Value = DLookup("strRegPassword", "tblRegions", "[lngRegID]=" & Me.cboREGION.Value) Then
lngMyRegID = Me.cboREGION.Value
'Close logon form and open enter leak form
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.Close acForm, "frmSELECT_REGION", acSaveNo
Else
MsgBox "Password Invalid.  Please Try Again", vbOKOnly, "Invalid Entry!"
Me.txtPassword.SetFocus
End If
'If User Enters incorrect password 3 times the database will shut down
intLogonAttempts = intLogonAttempts + 1
If intLogonAttempts > 3 Then
MsgBox "You do not have access to this database.Please contact the Databas Administrator.", vbCritical, "Restricted Access!"
Application.Quit
End If
Exit_cmdLogin_Click:
    Exit Sub
Err_cmdLogin_Click:
    MsgBox Err.Description
    Resume Exit_cmdLogin_Click
End Sub
fkegley
Yes, this can be done. Modify the command that opens the next form to this:
oCmd.OpenForm stDocName, , , stLinkCriteria,,,lngMyRegID
I think I have it right, but I am trying to put lngMyRegID into the OpenArgs argument of the open command.
Then in the form being opened, set the Row Source property to SELECT field list WHERE fieldname = " & Me.OpenArgs
LiLGaL
Frank,
neglected to mention that I'm new to VB also. This is the SQL for the List Box in my other form
SELECT qryCOMMUNITY.COMMUNITY, qryCOMMUNITY.REGION
FROM qryCOMMUNITY
Order/> neglected to mention that I'm new to VB also. This is the SQL for the List Box in my other form
SELECT qryCOMMUNITY.COMMUNITY, qryCOMMUNITY.REGION
FROM qryCOMMUNITY
ORDER BY qryCOMMUNITY.COMMUNITY;
Where exactly would I put the second information which you provided?
Thanks.
fkegley
It is easier to develop an example. It is attached. Query1 does all the work.
LiLGaL
Frank,
thank you for your help. I will try it now.
Thanks Again
fkegley
You're welcome. I am glad I could help. If you have any questions, I'll be happy to answer them, if I can.
LiLGaL
Frank,
I'm having problems. When I click the cboREGION and enter the region, then I enter the password, and click the GO button, two different parameter show up asking for Form!frmSELECT_REGION!cboREGION.
What am I doing wrong?
fkegley
It should be this:
orms!frmSELECT_REGION!cboREGION
LiLGaL
Sorry,
ts still not working. Any other suggestions?
fkegley
Check your spelling. Check it again! That usually appears when Access can't tell what something is.
LiLGaL
Frank,
finally got it to work. I then added my password code and now its not working. Below is my code. Can you help me out?
CODE
  
Private Sub Command3_Click()
On Error GoTo Err_Command3_Click
    Dim stDocName As String
    Dim stLinkCriteria As String
    stDocName = "frmENTER_LEAK_FORM"
    
'Check to see if data is entered into the RegionName combo box
If IsNull(Me.Combo0) Or Me.Combo0 = "" Then
MsgBox "You must select a Region.", vbOKOnly, "REQUIRED DATA"
Me.Combo0.SetFocus
Exit Sub
End If
'Check to see if data is entered into the password box
If IsNull(Me.txtPassword) Or Me.txtPassword = "" Then
MsgBox "You must enter a Password.", vbOKOnly, "REQUIRED DATA"
Me.txtPassword.SetFocus
Exit Sub
End If
'Check value of password in the REGIONS table to see if this
'matches value chose in the combo box
If Me.txtPassword.Value = DLookup("strRegPassword", "REGIONS", "[CustomerID]=" & Me.Combo0.Value) Then
CustomerID = Me.Combo0.Value
'Close logon form and open the frmENTER_LEAK_FORM
    DoCmd.Close acForm, "frmREGION", acSaveNo
    DoCmd.OpenForm stDocName, , , stLinkCriteria
Else
MsgBox "Password Invalid.  Please Try Again.", vbOKOnly, "INVALID ENTRY!"
Me.txtPassword.SetFocus
End If
'If User Enter incorrect password 3 times database will shut down
intLogonAttempts = intLogonAttempt + 1
If intLogonAttempts > 3 Then
MsgBox "You do not have access to this database.  Please contact the Databas Administrator.", vbCritical, "RESTRICTED ACCESS!"
Application.Quit
End If
Exit_Command3_Click:
    Exit Sub
Err_Command3_Click:
    MsgBox Err.Description
    Resume Exit_Command3_Click
    
End Sub
fkegley
What part of the code is not working? I can't tell from reading it.
LiLGaL
The code seems to be working fine.
When I open up the first form, I enter the region name, then the password, and I click the go button, to go to the other form. That where the problem comes up. A parameter pops up Forms!frmREGION!CustomerID.
fkegley
That usually means Access doesn't recognize a name. I would be suspicious that I had misspelled something. Edited by: fkegley on Wed May 24 9:17:53 EDT 2006.
Edited by: fkegley on Wed May 24 9:18:12 EDT 2006.
LiLGaL
Frank,
checked and double checked and triple checked. I can't find any misspelled words. I don't know what else to do and I'm on a deadline. Do you have any other suggestions?
fkegley
You can't close frmREGION until you have gotten the value of CustomerID from it. Rearrange your code so that in the Open event of frmENTER_LEAK_FORM, you close frmREGION.

FOr you could pass the value of CustomerID via the OpenArgs argument of the DoCmd.OpenForm command. Then in the Open event of frmENTER_LEAK_FORM, fetch it into a control on the form.
Edited by: fkegley on Wed May 24 10:44:59 EDT 2006.
LiLGaL
Frank,
Thank you, thank you, thank you, thank you..... I did the first suggestion that you had. It worked. Thank you so much for all you help and patience with this issue. This is a great website for beginners like me. I'm not just saying that because I got my stuff to work, but also because I ended up learning more than I started out with. I just want to say thanks to you, and everyone on here for all your help to people like me, just starting out.
Indirectly, I'm hoping this turns into a promotion for me...wish me luck *smile*
fkegley
You're welcome. I am glad I could help. I'll take some of your pay raise! grin.gif
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.