Expose column(1) rowsource of combo box bound to Junction table (1 Viewer)

coasterman

Registered User.
Local time
Yesterday, 17:52
Joined
Oct 1, 2012
Messages
59
I have a very simple continuous form form with two fields which are bound to the two keys in the junction table sitting between the Customer table and the Pcode tables

cboCusName - Rowsource is column(0) PK from customer table and column(1) the customer name
cboPcode - Rowsource is column(0) PK from Postcode table and column(1) the Postcode

Currently the datasource for the form is the tblCusPcode table but I want to change that to a query so I can seach from an unbound textbox which will have criteria

Like "*" & [frmCus!cboCus] & "*" for the customer but I now realise I cannot expose column(1) in the query directly.

I tried this found on another form

Code:
Public Function getComboCol(strForm, strCombo, intCol)

  getComboCol = Forms(strForm)(strCombo).Column(intCol)

End Function

"And in the criteria you put this:
=getComboCol("form_name","cbx_name",2)

but I was getting an "error in compiling this Function" after the query builder - this is what I had entered in the 3rd column of the qry as the Expression rather than the Criteria . I also tried it on the control source of an unbound text box in the detail section of the form itself just to see if I could get any result but again an error.

Cname:getComboCol(["Forms]![frmCus]","[cboCus]",1) I presume this is 0 based so my Customer name would be column (1)

I am no doubt doing something a bit dim so if anyone could set me right I'd be grateful

Thanks
 

Gasman

Enthusiastic Amateur
Local time
Today, 00:52
Joined
Sep 21, 2011
Messages
14,048
I *think* you just need the formname "frmCus" not the Forms reference?

Try printing the result of that function in the immediate window and see what is produced. That is where I try a lot of my formulae.

This works for me?
Code:
? getcombocol("Form1","combo16",1)
DEC
 

coasterman

Registered User.
Local time
Yesterday, 17:52
Joined
Oct 1, 2012
Messages
59
Thanks Gasman that was exactly the problem. Trying to keep the question as simple as possible I hadn't mentioned my frmCus is actually a subform on frmMain and that is where I got into a tangle over referencing. My issue is now how the pass that extra part to the function if the Function argument is simply the final "frmCus" name?

I get so fustrated with my lack of knowledge sometimes. Have some days where I think I am making good progress and overcome problems myself and at other tmes such as this I fell like I'm back at square one again
 

coasterman

Registered User.
Local time
Yesterday, 17:52
Joined
Oct 1, 2012
Messages
59
Hi, I have that page bookmarked but I don't think that is my problem with this particular task.

The above Function as I understand it is simply looking for the name of the form in the Forms collection rather than its name and location.

This would be the reference to the control (these are the actual names rather than those given in my original post)

Forms!FrmMain!frmSols!SolName.Column(1) and works fine in the immediate window but I just havent figured how to pass or modify the arguments of the function to get the '!frmMain!' part of the forms reference and not error
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 00:52
Joined
Sep 12, 2006
Messages
15,614
Sorry. I keep trying to add a new post, but I keep getting a server error
 

Gasman

Enthusiastic Amateur
Local time
Today, 00:52
Joined
Sep 21, 2011
Messages
14,048
Set some Tempvars and use those in your query. :)
 

bastanu

AWF VIP
Local time
Yesterday, 17:52
Joined
Apr 13, 2010
Messages
1,401
The easiest is to add a hidden textbox (i.e. txtCustomerName) on the subform, set its control source to Forms!FrmMain!frmSols!SolName.Column(1) then in your query reference it as Forms!FrmMain!frmSols!txtCustomerName.

Cheers,
 

coasterman

Registered User.
Local time
Yesterday, 17:52
Joined
Oct 1, 2012
Messages
59
Hi. I tried the 2nd sugestion as I'd seen that suggested elsewhere but when couldnt get it to display anything but the name of the first Company all the way down the continuous form.

I must have not done it right as having tried again with the correct referencing (the Text box I added to the Detail section is named txtSolsExtractedName) I now see a different company for each record in the new Text Box so thats progress. . Having said that when I went to add a third column to the query with the field Expression I still have a proble. This is my field in the QBE grid below:

Extracted:Forms!FrmMain!frmSols!txtSolsExtractedName

I see the txtSolsExtractedName still displays the appropriate company name on the form but opening the query (whilst form still open) the new 'Extracted' column just repeats the name of the first company for every record. I hadn't added any criteria at this point as just wanted to see if the company names were visible in the query first.

I aslo noted that anything I added to the Criteria (even the name of the repeated Company in the query was displaying) of the Extracted column returned no results and the quey was no longer updateable

I'm really not sure how this is going to work
 

jdraw

Super Moderator
Staff member
Local time
Yesterday, 20:52
Joined
Jan 23, 2006
Messages
15,364
Perhaps it is time to post a copy of the database with only enough data to show the issue. Make sure you give instructions to get to the problem.
Good luck.
 

bastanu

AWF VIP
Local time
Yesterday, 17:52
Joined
Apr 13, 2010
Messages
1,401
Is this what you're trying to do?

Cheers,
Vlad
 

Attachments

  • Database4.accdb
    476 KB · Views: 382

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 00:52
Joined
Sep 12, 2006
Messages
15,614
A combobox actually displays the first non-zero length column as the contents.
So I am not sure if this helps.

If you have a combobox for a person, say, with fields
ID, Surname, FirstName

You can actually make the query this instead
ID, FullName: [Surname] & " " & [firstname], surname, firstname, and have widths 0,0.01,3,3

The concatenated fullname is displayed, as the width of 0.01 is non-zero, but it effectively gets hidden in the drop down, because it's too narrow to show. The drop down just shows names sorted by surname and firstname - assuming you set the appropriate sorts.

You can also add other text boxes, and bind them to columns of the combo box. In the first example.
=mycombobox.column(1) will show the surname
=mycombobox.column(2) will show the firstname

because the combo box is zero based.
=mycombobox.column(0) will show the first ID column.
note that =mycombobox will actually show the bound column which is generally column 0, but doesn't have to be.
 

coasterman

Registered User.
Local time
Yesterday, 17:52
Joined
Oct 1, 2012
Messages
59
Hi. @ Vlad thank you for the example. The form behaviour is what I am looking for but I think I now see the issue I am having.

my junction table is just two fields -a composite key of SolID and PcodeID. (the 'Sols' are companies who will have multiple locations) I'm wondering now whether that is the issue I am having when trying to set a criteria in a query and still have the query updatable?

(I hadnt appreciated at the time that this might be an issues as my original form was simply using the tblSolPCode as the forms record source and no query . Searching that form used http://allenbrowne.com/AppFindAsUType.html module which worked until I had an instance on one machine where I was getting an 'Object not Defined' runtime error on opening the database and pointing at the above code module. I dont recall exactly where in the code now as not where that machine is presently .The odd thing is previous versions versions of the build which had previously worked fine on that machine also have now have the same runtime error on opening despite identical versions of office365 and same WIN10 build version)

Any way back to the form. This uses 'Not in list' event when a user come across an entirely new company and writes that to tblSol as which point they can then add the new record along with a selectable Pcode and the FK value are then written to the junction table. (The Pcode table is as far as the users concerned static and currently contains All UK Pcodes which would be maintained as an admin function along with the other tables in the hierarchy - counties, regions etc.)

It is not too late in the build for me to rebuild and populate the junction table to include a SolPcodeID PK field and then have a multi-field index on CustID and PcodeID to prevent duplicate combinations. Before I do so however I was wondering if there were any opinions whether this is generally the method which results in fewer issues

I have attached a screenshot of the relationship tables (or at least the section of interest) in case it better illustrates what I have described
 

Attachments

  • Screenshot 2021-03-13 113806.jpg
    Screenshot 2021-03-13 113806.jpg
    84.8 KB · Views: 337

bastanu

AWF VIP
Local time
Yesterday, 17:52
Joined
Apr 13, 2010
Messages
1,401
Something like this maybe?
Cheers,
 

Attachments

  • Database4.accdb
    536 KB · Views: 249

Users who are viewing this thread

Top Bottom