Open form based on subform ID: It's working, but why? :)

[ComicBookGuy]

Hi, I'm totally new to Access, googling my way forward, one line at a time..

I have a form (frmSearch), based on (I think) John Big Bootys code. On it, a search box, which filters results as you type. Works great.
Instead of presenting the search results in a listbox, I wanted it in a subform (sfmSearchresults). Also works great.

Problem was this: I wanted a button on this form, to open another form based on [CustID]. For a listbox, I could use columns(0), but not for a subform. After much trying I got this to work:

Private Sub cmdVisaPren_Click()
    
  Dim strWhere As String
  strWhere = "[CustID] = " & Me.sfmSearchResults.Form.Controls.Item(5)
  
  DoCmd.OpenForm "frmAddCust", acNormal, , strWhere, acFormEdit, acWindowNormal
     
End Sub

The part that I don't understand is the Item(5). The subform is based on a query (qrySearch), from tblCustomers. tblCustomers fields is as follows:
CustID, FirstName,LastName,Adress1,Newspaper1,Newspaper2.

Item(0) in strWhere would return Adress1 as [CustID].
Item (1) = Newspaper1, Item(2) = Newspaper2, Item(3) = FirstName, Item(4)=LastName, and (finally) Item(5)=CustID.

I don't get the logic. With listboxes, I could just count columns for columns(). If someone could explain the logic, I'd be happy!

Have attached my database if anyone wants to see. It's just a test database, with 4 people. The final database will have about 1200 people, and many more fields. Its .accdb (Access 2013). I tried converting to Access 2002-2003, but couldn't get it to work.

Thanks,
Martin

 

[MarvinM]

Martin,


I use A2007 and couldn't get it to open. So let me take a stab in the dark and maybe it will help you in your investigation.

1. Just because your table is arranged this way--CustID, FirstName,LastName,Adress1,Newspaper1,Newspaper2--doesn't mean that the query is too. Unless you did SELECT tblCustomers.*, it's possible you selected the columns in a different order.
2. Even if you have the columns in the query arranged in the same order, did you select them and drag them to the query grid in a different order? Delete? Insert? Rearrange at all?
3. On the subform, did you use datasheet view or did you place fields in the order you want? Did you rearrange any controls there?
4. What is the tab order of the controls on your subform?

Sorry I can't get to the bottom of this without being able to open your database. Maybe you can find something in there with these ideas.

Good luck.
_________________
Regards,
Marvin M
Windows 7 Professional, MS Access 2007/2010
Windows 8 Professional, MS Access 2013
---------------------------------------------------------------------------------------------
If my post has helped you, please click the scales or click the 'Thumbs up'. Thanks!
---------------------------------------------------------------------------------------------

 

[Mihail]

Marvin is right.
I try to add more info:
In order to understand the logic, do a try:
Copy your listbox RowSource (CTRL+C)
Start to create a query but cancel the Add Tables window and switch to SQL view.
Paste what you have copied (CTRL+V)
Run the query and look to the "columns" order.
Your "Cust_ID" should be the 5 column (start to count from zero).

 

[ComicBookGuy]

Thank you, both of you. Makes sense. Will check Monday.

Cheers!

 

[ComicBookGuy]

Hmm. this is the SQL from listbox RowSource:

SELECT qrySearch.[CustID], qrySearch.LastName, qrySearch.FirstName, qrySearch.Address1, qrySearch.Newspaper1, qrySearch.Newspaper2 FROM qrySearch ORDER BY qrySearch.[LastName], qrySearch.[FirstName];

The subform sfmSearchResults is based on qrySearch, with the following SQL:

SELECT tblCustomers.CustID, tblCustomers.LastName, tblCustomers.FirstName, tblCustomers.Address1, tblCustomers.Newspaper1, tblCustomers.Newspaper2
FROM tblCustomers
WHERE (((tblCustomers.FirstName) Like "*" & [forms]![frmSearch]![SrchText] & "*")) OR (((tblCustomers.LastName) Like "*" & [forms]![frmSearch]![SrchText] & "*"))
ORDER BY tblCustomers.LastName;

Still don't understand the logic. Item (0) is Adress1 for example. Might very well be me being daft Anyway, it's working!
Thanks, both of you.

Martin