Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Access Select Multiple Records on List and Transfer to Subform

Posted on 2009-05-12
9
Medium Priority
?
352 Views
Last Modified: 2013-11-28
Greetings:

I have a main form (unbound)  that contains a list that is generated from several search fields.  Currently, I can click on the record, and press a command button that will copy that record information over to a bounded subform.  Works great!  However, looks like we would like to be able to select multiple record on the list and have those records be copied over to the subform for browsing.

Here is the code in the list table review subform(frmBrowseByName_Single):
Private Sub Command83_Click()
DoCmd.OpenForm "frmBrowseByName_Single", , , "[StructureID]=" & Me.lstStructures
On Error GoTo Err_Command83_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmBrowseByName_Single"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command83_Click:
    Exit Sub

Err_Command83_Click:
    MsgBox Err.Description
    Resume Exit_Command83_Click
   
End Sub

Here is the code in the subform (frmBrowseByName_Single):
Private Sub Form_Load()
'Used to allow for next record to be browsed
Dim rstFormRecords As Object
Dim lngStructureID As Long
   
    lngStructureID = Me.StructureID
    Me.FilterOn = False
   
    Set rstFormRecords = Me.Recordset.Clone
    rstFormRecords.FindFirst "[StructureID] = " & lngStructureID
    If Not rstFormRecords.EOF Then Me.Bookmark = rstFormRecords.Bookmark

End Sub

If I set the multiple select to extended, and the code stopped at the StructureID =.  

Thanks for the help!!

heather
0
Comment
Question by:Heather_D
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 5

Expert Comment

by:josephwalsh
ID: 24367007
Take the query that generates the list.
That is also the basis of your recordsource for the other form.

Therefore create your new query as follows (set this as the recordsource for the other form):
SELECT * FROM yourtablename/queryname
WHERE [StructureID] IN yourexisting searchquery
0
 

Author Comment

by:Heather_D
ID: 24407424
Hi Joseph,

Not sure I understand.  Here is my search query:

SELECT tblNCHPOsurvey.StructureID, LTrim([PropertyFirstName] & " " & [PropertyName] & " " & [PropertyTown]) AS NameandType, tblNCHPOsurvey.COUNTY, LTrim([LocationPrefix] & " " & [PropertyLocation] & " - " & [PropertyTown]) AS AddressTown, tblNCHPOsurvey.SSN, tblNCHPOsurvey.PropertyName, tblNCHPOsurvey.PropertyFirstName, tblNCHPOsurvey.PropertyTown, tblNCHPOsurvey.NRCODE, tblNCHPOsurvey.LocationPrefix, tblNCHPOsurvey.PropertyLocation, tblNCHPOsurvey.PropertyTown, tblNCHPOsurvey.Style, tblNCHPOsurvey.Material, tblNCHPOsurvey.Construction, tblNCHPOsurvey.Comment, tblNCHPOsurvey.Date, tblNCHPOsurvey.ListStatus
FROM tblNCHPOsurvey
WHERE (((LTrim([PropertyFirstName] & " " & [PropertyName] & " " & [PropertyTown])) Like "*" & [forms]![frmSearch]![txtNameandType] & "*") AND ((tblNCHPOsurvey.COUNTY) Like "*" & [forms]![frmSearch]![cmbCounty] & "*") AND ((LTrim([LocationPrefix] & " " & [PropertyLocation] & " - " & [PropertyTown])) Like "*" & [forms]![frmSearch]![txtPropertyLocation] & "*") AND ((tblNCHPOsurvey.PropertyTown) Like "*" & [forms]![frmSearch]![txtTown] & "*") AND ((tblNCHPOsurvey.NRCODE) Like "*" & [forms]![frmSearch]![txtNRCode] & "*") AND ((tblNCHPOsurvey.Style) Like "*" & [forms]![frmSearch]![txtStyle] & "*") AND ((tblNCHPOsurvey.Material) Like "*" & [forms]![frmSearch]![txtMaterial] & "*") AND ((tblNCHPOsurvey.Construction) Like "*" & [forms]![frmSearch]![txtConstruction] & "*") AND ((tblNCHPOsurvey.Comment) Like "*" & [forms]![frmSearch]![txtComment] & "*") AND ((tblNCHPOsurvey.Date) Between Nz([Forms]![frmSearch]![txtEarlyDate],[Date]) And Nz([Forms]![frmSearch]![txtLateDate],[Date])) AND ((tblNCHPOsurvey.ListStatus) Like "*" & [forms]![frmSearch]![cmbListStatus] & "*"))
ORDER BY tblNCHPOsurvey.COUNTY, tblNCHPOsurvey.PropertyName, tblNCHPOsurvey.PropertyFirstName, tblNCHPOsurvey.PropertyTown;

THanks,

Heather
0
 

Author Comment

by:Heather_D
ID: 24422927
Do you need a copy of the database?  

Any suggestions would be greatly appreciated!

Thanks,

Heather
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Heather_D
ID: 24423169
DoCmd.OpenForm "frmBrowseByName_Single", , , "[StructureID]=" & Me.lstStructures

If I change the list select to extended, I get an error in the above code.  Looks like it might have something to do with the structureid being = to something instead of multiple things.
0
 
LVL 5

Expert Comment

by:josephwalsh
ID: 24424199
A copy of the datbase would helpful.
Thanks
Joe
0
 

Author Comment

by:Heather_D
ID: 24424267
This is the database.  Go to switchboard form and click on search/browse by Name.  The search Name form (frmsearch)  is my original form and then when you click look record, that form is the subform(frmbrowsebyname_single).

expert-exchange.zip
0
 
LVL 5

Expert Comment

by:josephwalsh
ID: 24429774
Thanks
0
 
LVL 5

Accepted Solution

by:
josephwalsh earned 2000 total points
ID: 24429811
I have attached an updated database.
JPW has been put in a comment where changes have been made.

I made changes to the search form.
The listbox has been changed to allow multiple select.
There is new code in 'View a Record' where it builds the filter for the subform.
Option explicit has been added to give more control over variable defs etc.

I made a few small changes to frmBrowseByName_Single to allow the filter to work correctly.

Regards,
Joe
expert-exchangenew.zip
0
 

Author Closing Comment

by:Heather_D
ID: 31580587
Super!! I love you!
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question