How to open diff forms depending on selection in a listbox? 2

[Jrs2nd]

Essentially, I have a listbox ,based on a union query, on a form {whose recordsourse is different from the listbox}
which displays records from 3 tables. the RecordNumber
from each table has a unique Identifying letter as the
first character in the record number itself:
ie.. tbl1 REC# = D000001 | tbl2 REC# = G000001 |tbl3 REC# = F000001.
What I wish to acheive is: upon selecting (Clicking on)
a particular record(row)in the Listbox, and depending on
which letter the RecordNumber starts with,open a form which
corresponds to the table in which the record resides.

The RecordNumber is the first Column being displayed in the
listbox and is the primarykey for the respective tables the
records are "pulled" from.

However, I'm not sure how to specify the following:
The Column (RecordNumber)in the row selected(clicked on)
Checking which "letter" the Rec# starts with.

below is a "code" logic example of what I hope to acheive.
any suggestions on where to look or research how to do
this??

Private Sub List35_Click()
On Error GoTo List35_Click_err
If (IsNull([RecordNumber]) Or [RecordNumber] = "&quot Then
GoTo List35_Click_Exit:

ElseIf [RecordNumber] = "D" Then
DoCmd.OpenForm "frmPCADomestic", acNormal, , , acFormAdd

ElseIf [RecordNumber] = "G" Then
DoCmd.OpenForm "frmPCAGlobal", acNormal, , , acFormAdd

ElseIf [RecordNumber] = "F" Then
DoCmd.OpenForm "frmFYINotices", acNormal, , , acFormAdd

Else

End If


List35_Click_Exit:
Exit Sub

List35_Click_err:
MsgBox Err.Number
MsgBox Err.Description
Resume List35_Click_Exit

End Sub

Thank you,
Jim

 

[nickjar2]

try something like: (apologies but I don't have Access on my home PC)

Private Sub List35_Click()
dim sRec as string

sRec = list35.value
'(not sure about the value bit but you should get a drop down once you have typed in the '.' just scroll down until u find a suitable property (maybe Text or selectedItem))

srec = mid$(srec,1,1) ' this will give you the 'D','G' or 'F'

select case srec

case "D"

docmd.openform "frmPCADomestic"

case "G"

docmd.openform "frmPCAGlobal"

etc etc

end select

 

[Aivars]

Hi!

For opening of forms by using listbox or combobox I create listbox/combobox what rowsource include form (or report) name and descriptions. In first row I put form name, in second one - description.

Private Sub List35_DblClick(Cancel As Integer)
DoCmd.OpenForm List35
end sub

For creating of listbox rowsource I use form/report names from system table and them description (form/report properties on DB window). Description are required.

Private Sub Form_Load()
me.List35.rowsource=ReportsList("frm",,,"Forms&quot
All forms which contain "frm" will appear on listbox
End Sub

This is function for reports/forms listbox rowsource creating.

Function ReportsList(Optional strLikeReportName As String = "", Optional strReportList As String = "", Optional intStartPos As Integer = 1, Optional strContainer As String = "Reports&quot As String
On Error GoTo Err_ReportsList
'strLikeReportName - part of report name which is same all needed reports
'strReportList - exist listbox rowsource (text such "rptMyReport";"This is my report" what you want to keep in the new rowsource
'strContainer="Forms" for forms (must be included in called command)
Dim rst As Recordset
Dim strSQL As String
Dim strWhere As String
Dim strDescription As String
Dim intLenght As Integer
Dim prp As Property

intStartPos = Abs(intStartPos)
intLenght = Len(strLikeReportName)

strSQL = "SELECT MSysObjects.Name FROM MSysObjects "
If strContainer = "Reports" Then
strWhere = "WHERE MSysObjects.Type = -32764 "
Else
strWhere = "WHERE MSysObjects.Type = -32768 "
End If

If strLikeReportName <> &quot;&quot; Then
strWhere = strWhere & &quot;And Mid(Name,&quot; & intStartPos & &quot;,&quot; & intLenght & &quot = '&quot; & strLikeReportName & &quot;' &quot;
End If
strSQL = strSQL & strWhere & &quot;ORDER BY MSysObjects.Name;&quot;

Set rst = CurrentDb.OpenRecordset(strSQL)
If Not rst.EOF Then
While Not rst.EOF
strDescription = CurrentDb.Containers(strContainer).Documents(rst(0)).Properties(&quot;Description&quot.Value
If strReportList <> &quot;&quot; Then
strReportList = strReportList & &quot;;&quot;
End If
strReportList = strReportList & rst(0) & &quot;;&quot; & strDescription
rst.MoveNext
Wend
End If
rst.Close
Set rst = Nothing
ReportsList = strReportList

Exit_ReportsList:
Exit Function

Err_ReportsList:
If Err.Number = 3270 Then 'Property not found.
MsgBox &quot;You need to write descriptions of &quot; & LCase(strContainer) & &quot;!&quot;, vbCritical, &quot;Property not found&quot;
ReportsList = &quot;Property not found&quot;
Else
MsgBox &quot;Error No &quot; & Err.Number & vbLf & Err.Description, , &quot;Public Function ReportList&quot;
End If
Resume Exit_ReportsList
End Function


Maybe this will help you.
Aivars

 

[Jrs2nd]

Hey NickJar2 and Aivars,

Thank you very much for the Quick responses and helpful
replys!!!

NickJar2, your code was perfect down to the .Value !
I finished off the cases and added the error handling and
it's smooth sailing!!

Aivar, That code is extremely useful!!! I have used it
for a Master Maintenance Form and it's better than a
switchboard. also will use it for an available reports list.

thanks again both of you!
I do like these forums, stuff that has me beating my head
against the wall for hours/days, then someone helpful like
each of you, solve quickly and help with enhancing my db
in away I'd never have thought of.

Yours,
Jim