Open 3 forms using option and combo?

[jlnewbie]

I want to open Clients, Site or Buildings forms depending on user selection.
Ex. choose option1 (clients)
and have combo select existing clients,
then open the form to that selection.
I've found an address db with the this code but it only opens one form and I'm not too well versed on VBA.
Thank you



JLopez
Lopez31@ATC-Enviro.com
Environmental Services @ Your Service

 

[PHV]

And what have you so far ?

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

 

[jlnewbie]

Thanks PHV for responding. Below is the code that I modified from that address db I told you about. And it seems to work well in opening the one form I need but I get two errors with it. One, my clientId show as a number not a name and the data are not filtered. But finally, I would really like to open three separate forms as indicated earlier and filter to selection. I'm just needing a little guidance/help I'm not looking to have somebody do my work.
Thank you


Option Compare Database
Option Explicit

Private mstrSQL As String

Private Sub cmdClose_Click()
On Error Resume Next
DoCmd.Close
End Sub

Private Sub cmdGo_Click()
' Requery the Sites form based on
' the selected items

On Error GoTo HandleErr

DoCmd.OpenForm "Sites"
With Forms!Sites
If Len(Me!cboSelect & "") > 0 Then
' Construct SQL for Sites's Recordsource
Select Case optChoose
Case 1
' Site name
mstrSQL = "SELECT * FROM dbo_tblSites Where " _
& " SiteDescription Like '*" & DoubleQuote(Me![cboSelect]) & "*'"
Case 2
' Site Number
mstrSQL = "SELECT * FROM dbo_tblSites WHERE " _
& "SiteID In (SELECT DISTINCTROW " _
& "SiteID FROM SiteNumber WHERE SiteNumber Like '*" _
& DoubleQuote(Me![cboSelect]) & "*')"
Case 3
' Client
mstrSQL = "SELECT * FROM dbo_tblSites WHERE " _
& "SiteID In (SELECT DISTINCTROW " _
& "SiteID FROM ClientID WHERE ClientID Like '*" _
& DoubleQuote(Me![cboSelect]) & "*')"

Case Else
End Select
.RecordSource = mstrSQL
!cmdFind.Caption = "&Show All"

Else
.RecordSource = "dbo_tblSites"
End If
End With
DoCmd.Close acForm, "FindForm"

ExitHere:
Exit Sub

HandleErr:
Select Case Err
Case Else
MsgBox Err & ": " & Err.Description, vbCritical, _
"Error in Form_FindForm.cmdGo_Click"
End Select
Resume ExitHere
Resume
End Sub

Private Sub optChoose_AfterUpdate()
' Populate rowsource of cboSelect

Dim strSQL As String

On Error GoTo HandleErr

Select Case optChoose
Case 1
' Site name
strSQL = "Select Distinct SiteDescription from dbo_tblSites " _
& "Order By SiteDescription"
Case 2
' Site Number name
strSQL = "Select Distinct SiteNumber from dbo_tblSites " _
& "Where SiteNumber Is Not Null Order By SiteNumber"
Case 3
' Client name
strSQL = "Select Distinct ClientID from dbo_tblSites " _
& "Where ClientID Is Not Null Order By ClientID"

Case Else
End Select

With Me!cboSelect
.Value = Null
.RowSource = strSQL
.Requery
.Value = .ItemData(0)
End With

ExitHere:
Exit Sub

HandleErr:
Select Case Err
Case Else
MsgBox Err & ": " & Err.Description, vbCritical, _
"Error in Form_FindForm.optChoose_AfterUpdate"
End Select
Resume ExitHere
Resume
End Sub

Private Function DoubleQuote(strIn As String) As String
Dim i As Integer
Dim strtemp As String
For i = 1 To Len(strIn)
If Mid(strIn, i, 1) = "'" Then
strtemp = strtemp & "''"
Else
strtemp = strtemp & Mid(strIn, i, 1)
End If
Next i
DoubleQuote = strtemp
End Function




JLopez
Lopez31@ATC-Enviro.com
Environmental Services @ Your Service