How to select Rec from listbox on Frm1 & open Frm2 diplaying the Rec 1

[Jrs2nd]

If you were not able to understand my shorthand:

How to have a record selected from a listbox on one form
open another form displaying the selected record?

I presently have the following code, (in the onDblClick
event of the ListBox)which does open the correct form
properly. ( there are 4 forms with select case deciding
which one should be opened) However, I can not figure out
how to use the selected record to "goto" the same record
in the second form.

CODE:


Private Sub List35_DblClick(Cancel As Integer)

On Error GoTo List35_DblClick_err
Dim sRec As String

sRec = List35.Value

sRec = Mid$(sRec, 1, 1) ' this will give the 'D','G','A', or 'F' (1st character of an alphanumeric id number{primary key}for the record)

Select Case sRec
Case "A"

DoCmd.OpenForm "frmDataEntry", , , , acFormEdit, , [RecordNumber] = [Forms]![FrmSprvsr]![List35].Value


Case "D"

DoCmd.OpenForm "frmPCADomestic", , , , acFormEdit, , [RecordNumber] = [Forms]![FrmSprvsr]![List35].Value

Case "G"

DoCmd.OpenForm "frmPCAGlobal", , , , acFormEdit, , [RecordNumber] = [Forms]![FrmSprvsr]![List35].Value

Case "F"

DoCmd.OpenForm "frmFYINotices", , , , acFormEdit, , [RecordNumber] = [Forms]![FrmSprvsr]![List35].Value

Case ""

GoTo List35_DblClick_Exit:

End Select



List35_DblClick_Exit:
Exit Sub

List35_DblClick_err:
MsgBox Err.Number
MsgBox Err.Description
End Sub

I'm not sure whether the "Whereclause" or "OpenArgs" should
be used. Or if I should call somekind of Function.

I have tried the above code, it does not "goto" the
selected record.

any suggestions?

Thanks,
Jim

 

[Guest_imported]

Try putting [RecordNumber] = [Forms]![FrmSprvsr]![List35].Value in the Where clause of the open statement. By putting it in the where clause, the form is opened filtered to the specific record(s).

DoCmd.OpenForm "frmFYINotices", acNormal, , [RecordNumber] = [Forms]![FrmSprvsr]![List35].Value

 

[braindead2]

assuming srec holds the recordnumer you want then the code should be
docmd.openform "formname",,,"record number = " & Srec,acformedit

 

[Jrs2nd]

Hi Miners74,

It did "filter" the form, however the record did not display
I have a textbox that contains the following code:

='Record ' & [CurrentRecord] & ' of ' & Count(*)

which shows me the number of the record "out of" the
'filtered(or not)" recordset. when ( after DblClicking on
the record in the list in the other form) the form opens it shows: " 1 of 0" like I was about to enter the very 1st
record. clicked on "remove filter" button and it shows the
correct number of records on file.

I checked for any code in the form that might "unfilter"
it on opening but.. nada.. Is there any way to call a
function that uses code something like:

Me.RecordsetClone.FindFirst "[RecordNumber] = '" & [Forms]![FrmSprvsr]![List35].Value & "'"
Me.Bookmark = Me.RecordsetClone.Bookmark

or am I barking up the wrong logic tree ?

Thanks
Jim

 

[Jrs2nd]

braindead2,
Thank you for responding.

unfortunately sRec only gives the 1st character in an
alphanumeric id number.. ie "G"0001 or "A"01571. but
I like your idea, any way to incorporate it with my 1st
response to Miners74 suggestion? it may be something simple
but being simple myself, "I can't see the simple forest,
for the simple trees." or something like that.

thanks,

Jim

 

[Guest_imported]

I think I am understanding what you are wanting to do. Adding this code to your previous code:

Dim Str As String
Dim FilterInfo As String

'Put these lines after this statement
' sRec = Mid$(sRec, 1, 1)

Str = "Like" & Chr(34) & sRec & "*" & Chr(34)
FilterInfo = "(YourTableName.YourIDNumberField)" & Str

'Then add this code to the appropriate select clause:
DoCmd.OpenForm "YourFormName", acNormal, , FilterInfo

This code will bring up any record in the appropriate form that has a prefix letter of what is chosen. Is that what you are wanting?

 

[braindead2]

Miners74 and I are both telling you the same thing.

the first question I need to ask is which column in the list box holds the recordnumber
then
add these 2 lines towards the front of your code

dim filterinfo as string

strcrit = [list1].[column](0)

replace the zero with which ever column number the information is in. Remember column numbers start with zero
colunm(0) colunm(1) colunm(2)

then you open criteria will read

DoCmd.OpenForm "YourFormName", acNormal, ,"recordnumber = " filterinfo

and yes the way you describe above using bookmarks will also work but not until you are able to successfully pass the right criteria

 

[Jrs2nd]

I am dysfunctional today,

Not sure if this makes a difference but the Listbox is
based on a Union query that combines 4 tables which have
about 25% of the fields in common, hence the "opening of the forms in selectcase portion.
Would this mean another select case for the :

FilterInfo = "(YourTableName.YourIDNumberField)" & Str
Portion of the code???

Also:the RecordNumber column is the first therefore (0).


Using Miners74's method I get the same result as I did before except it's not "filtered" to "1 of 0".
Code added:
Dim Str As String
Dim FilterInfo As String

sRec = List35.Value
sRec = Mid$(sRec, 1, 1) ' this will give you the 'D','G' or 'F'

Str = "Like" & Chr(34) & sRec & "*" & Chr(34)
FilterInfo = "(PCADomestic.[RecordNumber])" & Str

Case "D"

DoCmd.OpenForm "frmPCADomestic", , FilterInfo

using Briandead2's method I rec'd the following error:
code added:

dim filterinfo as string
strcrit = [list1].[column](0)

Case "G"

DoCmd.OpenForm "frmPCAGlobal", acNormal, , "[Recordnumber] = " & FilterInfo, acFormEdit

**** used FilterInfo = "(PCAGlobal.[RecordNumber])" & strcrit also Dim strcrit As String****

Err.Number: 3075
"Syntax Error (missing operator) in Query expression
'[RecordNumber]=(PCAGlobal.[RecordNumber])G00013

I use the corresponding tablename for each form selected
I only changed one case for each code example and made sure
the record selected "matched" the case.

in Braindead2's even though there was an error I see it
correctly identified the RecordNumber: G00013 that
I had chosen for the test from the listbox.

Okay just how Dumb am I today.. any suggestions on what I'm
missing????

Thanks again to both of you for taking the time to reply and
for trying to drill some logic into my brain..

Jim

 

[braindead2]

After reading your post again I notice that although the field is called recordnumber you refer to it as an alphanumeric field meaning it must be a text field which means the text needs to be enclosed in qoutes. Change the line to read

DoCmd.OpenForm "frmPCAGlobal", acNormal, , "[Recordnumber] = '" & FilterInfo & "'", acFormEdit

lets see if that flys

 

[Jrs2nd]

Hey Braindead2,

Once again thank you for taking the time to look at this
for me!!

I added the recordset info so the Findinfo would use
the correct table for its corresponding form. However I
must have something coded in the Form Module that is
preventing the selected record from displaying.. even with
your addition of the " " quote marks ( because of it being
text for the id#) it still opens the form and displays the
1st record in the table. I've checked the Open, Load, Activate, GotFocus, Before
& After Update events for something that might remove or
over-ride the filter info we're trying to use.. but nothing!
(okay at least as far as my limited knowledge goes there isn't)

here's the present code I'm working with:

Private Sub List35_DblClick(Cancel As Integer)
On Error GoTo List35_DblClick_err
Dim rst1 As Recordset, rst2 As Recordset, rst3 As Recordset, rst4 As Recordset
Dim db As Database
Set db = CurrentDb
Set rst1 = db.OpenRecordset("DataEntryTBA", DB_OPEN_DYNASET)
Set rst2 = db.OpenRecordset("PCADomestic", DB_OPEN_DYNASET)
Set rst3 = db.OpenRecordset("PCAGlobal", DB_OPEN_DYNASET)
Set rst4 = db.OpenRecordset("tblFYINotices", DB_OPEN_DYNASET)
Dim frmPCADomestic As Form
Dim frmPCAGlobal As Form
Dim frmFYINotices As Form
Dim FilterInfo As String
Dim strcrit As String
Dim sRec As String
Dim Str As String ' for Miners74 suggested method

' this gives you the ID# of the record selected from the listbox
strcrit = [List35].[Column](0)

' ' the following two lines of code will give you the 'A','D','G' or 'F' portion
' of the AlphaNumeric ID# for selecting which form to open
sRec = List35.Value
sRec = Mid$(sRec, 1, 1)

'Str = "Like" & Chr(34) & sRec & "*" & Chr(34)'for Miners74 suggested method

Select Case sRec 'Select the right form

Case "A"

' ' tells which record should be displayed, upon opening the form
FilterInfo = "(rst1.[RecordNumber])" & strcrit
' 'open the form to the specified record
DoCmd.OpenForm "frmDataEntry", , , "[Recordnumber] = '" & FilterInfo & "'", acFormEdit

Case "D"

FilterInfo = "(rst2.[RecordNumber])" & strcrit
DoCmd.OpenForm "frmPCADomestic", , , "[Recordnumber] = '" & FilterInfo & "'", acFormEdit


Case "G"

FilterInfo = "(rst3.[RecordNumber])" & strcrit
DoCmd.OpenForm "frmPCAGlobal", , , "[Recordnumber] = '" & FilterInfo & "'", acFormEdit

Case "F"

FilterInfo = "(rst4.[RecordNumber])" & strcrit
DoCmd.OpenForm "frmFYINotices", , , "[Recordnumber] = '" & FilterInfo & "'", acFormEdit

Case ""

GoTo List35_DblClick_Exit:

' rst.FindFirst FilterInfo POSSIBLITIY???

rst1.Close
rst2.Close
rst3.Close
rst4.Close

Set db = Nothing

GoTo List35_DblClick_Exit:

End Select

List35_DblClick_Exit:
Exit Sub

List35_DblClick_err:
MsgBox Err.Number
MsgBox Err.Description
End Sub

is Findfirst an option??? grasping at straws here...

Jim

 

[braindead2]

I apologize I thought that you were attempting to open a from that was already bound to a recordsource. But after looking at the above code I perhaps misunderstood.
If you are trying to open bound forms them opening the recordsets is unnecessary and all you need is code something like below

Private Sub List35_DblClick(Cancel As Integer)
Dim strcrit As String
Dim strfrmname As String
strcrit = [List35].[Column](0)
sRec = Mid$(strcrit, 1, 1)
Select Case sRec
Case "A"
strfrmname = "frmDataEntry"
Case "D"
strfrmname = "frmPCADomestic"
Case "G"
strfrmname = "frmPCAGlobal"
Case "F"
strfrmname = "frmFYINotices"
Case ""
Exit Sub
End Select

DoCmd.OpenForm strfrmname, acNormal, , "recordnumber ='" & strcrit & "'"
End Sub

however if the forms you are opening are not bound to the tables
DataEntryTBA, PCADomestic, PCAGlobal, tblFYINotices then The advice I've been giving is incorrect.

you will need code more like what you have above but you need to let the form know where its recordset is coming from
something like below (non working code)

Case "A"

DoCmd.OpenForm "frmDataEntry"
strsql = "Select * from dataentryTBA where recordnumber = '& strcrit & "'"
froms!dataentry.recordsource = strsql

or using DAO
set rst = currentdb.openrecordset("dataentry&quot
rst.findfirst(recordnumber = critstr)
me.field1 = rst.field1
me.field2 = rst.field2

hopefully I have not just caused greater confussion. But unless I'm confused(often the case) I see no need to open 4 record sets when the form can only use 1.
All you really want to do is open the from with the correct recordset in it
Good luck

 

[Jrs2nd]

Hey Braindead2!!!!!
the following code that you recommended(with the two additions in red} worked perfectly!!!
no need to apologize... if any one should, it should be me
for not explaining myself clearly enough!! your code is soo
ooo much more elegant than the maze like mess I came up with
thank you very much!!

Private Sub List35_DblClick(Cancel As Integer)
Dim strcrit As String
Dim strfrmname As String
Dim sRec As String

strcrit = [List35].[Column](0)

sRec = List35.value
sRec = Mid$(strcrit, 1, 1)
Select Case sRec
Case "A"
strfrmname = "frmDataEntry"
Case "D"
strfrmname = "frmPCADomestic"
Case "G"
strfrmname = "frmPCAGlobal"
Case "F"
strfrmname = "frmFYINotices"
Case ""
Exit Sub
End Select

DoCmd.OpenForm strfrmname, acNormal, , "recordnumber ='" & strcrit & "'"
End Sub

Jim

 

[Jrs2nd]

PS.
Braindead2, you should join the service/forums.. costs
nothing and I could vote your tip as very helpful(to me at least!)

just a thought.. )
Jim

 

[braindead2]

you are correct I missed

Dim sRec As String

but note that

strcrit = [List35].[Column](0) 'gets the value

sRec = List35.value ' This line is doing nothing for you
sRec = Mid$(strcrit, 1, 1) 'srec gets its value from strcrit

and I am a registered member but each time I navigate between screens I get an error saying my admin has restricted access to (various sites) I have tried to shut off ad's etc.. but to no avail. So I just go in as guest and when I need to use my member advantages I log in.

 

[Jrs2nd]

Thanks again,
Know how Admins are..... study in paranoia

Jim