Open form from DblClick

[MackMan]

ok, I know this has been covered before, but I'm not sure how to word it in the search forums...

I have a main form. It's only contents is an unbound combobox, and it's used to filter down (via a query) the details in it's sub form (I'm using it to load different account information, rather then type it in all the time).

Anyway, I also have another form (Dashboard, or mainmenu) which summarises activity within Each account and is displayed via a datasheet.

What I'm trying to do is double click the accountname (on the main menu)which will then open up the necessary form and load the value from the datsheet accountname, in to the newly opened forms cbo, where I can apply the form to requery on load.

I'm guess I'm going to have to DIM the value, and docmd.openform etc...
but not sure how this would be written.

Once again, always appreciative on advise and assistance.

 

[CJ_London]

it is very straightforward - here is a link to the openform command

https://msdn.microsoft.com/en-us/library/office/ff820845.aspx

as a minimum, you would need to complete the formname and where condition to load the required account when the form opens

The where condition would look something like

"[AccountName]='" & me.accountname & "'"

or for numbers

"[AccountID]=" & me.accountid

 

[MackMan]

Hi CJ many thanks (Once again)

however, the form I want to open is opening, but the combobox I need populated remains blank...

I've tried...

DoCmd.OpenForm "frmMain", , , "[cboAccountSelect]= '" & Me.Account & "'"

AND

DoCmd.OpenForm "frmMain", , , "[cboAccountSelect]= '" & Me.Account.Value & "'"

AND

DoCmd.OpenForm "frmMain", , , "[cboAccountSelect]= " & Me.Account

With no luck.

Obviously the destination form can't find a value from the original form to populate the other combobox.

I'm missing something so obvious I can't see the wood for the trees.

The investigation continues....

 

[CJ_London]

This

"[cboAccountSelect]= '" & Me.Account & "'"

as a filter, implies you have a field called cboAccountSelect which I doubt - it needs to be the controlsource of the cboAccountSelect control - which needs to be the same type as me.account.

If me.account is numeric you would use your 3rd option.

What I'm trying to do is double click the accountname (on the main menu)which will then open up the necessary form and load the value from the datsheet accountname, in to the newly opened forms cbo, where I can apply the form to requery on load.

If the above does not solve your problem, you need to clarify what you mean here, I thought you just wanted to open a form for a particular account. All handling of the controls in the form would be in the form open/load/activate or current events. What is the cbo? what is its rowsource and why is it apparently different for each account? if it is 'filtered' for a particular account, then you would include that in the rowsource referencing a control on the form.

 

[MackMan]

Hi CJ. Many thanks again for your prompt reply. My apologies. I wasn't clear enough in my original post.

My current setup is...

frmMain - (Currently loaded by a hyperlink on frmMainMenu)- in the header sits an unbound combobox; [cboAccountSelect] and all it does is list a number of accounts. This is the only control on the form, and it's data comes from the table tblAccounts. where the row source is ...

 SELECT [tblAccounts].[AccountID], [tblAccounts].[Account] FROM tblAccounts ORDER BY [Account];

frmTopLine - is a subform on frmMain, and is used to enter various topline transactions. This is linked to [cboAccountSelect] via it's [AccountID] field in the Link Master and Link Child fields in the All tab. I've done it this way, as It's easier, rather than having to select the account name each time an entry is created.
If there is an easier way I am certainly open to suggestions!

On frmMainMenu sits another form; frmAccountSummary, and it's data is from a query, and is used to summarise various activity within any accounts that are in use. The first field within this datasheet is the Account.

What I'd like to do is double click the Account field and have it automatically populate the unbound combobox, within frmMain, rather than have to open the form, and then select an account.

Am I right in thinking that because the summary is based on a query, when I use the docmd openform based on the where condition, the unbound combobox remains blank?

This is a grey area for me.

 

[CJ_London]

still confused

 frmMain - (Currently loaded by a hyperlink on frmMainMenu)- in the header sits an unbound combobox; [cboAccountSelect] and all it does is list a number of accounts. This is the only control on the form

OK, so one control but then you say

frmTopLine - is a subform on frmMain

which is another control, so the combobox is not the only control - or is it? please clarify

 What I'd like to do is double click the Account field

is this in frmAccountSummary?

And is frmAccountSummary a subform on frmMainMenu? - you say it 'sits on'

automatically populate the unbound combobox

is this [cboAccountSelect]? and populate with what?

 

[MackMan]

Sorry.. does this help?


dblClick highlight field on one form;frmMainMenu to open up and populate cbo on another form; frmMain...

Forgive me if I sounded a bit stupid . I forgot that subforms are also controls..

 

[CJ_London]

All is clearer (I think)

in the doubleclick event of the account control in frmMainMenu put

docmd.OpenForm "frmMain",,,,,,account

this populates an object called openargs in frmMain

Then in open event of frmMain put the following

 cboAccountSelect=me.openargs

then, if not already done, in the frmTopline subform control set

 linkchild to accountID
 linkmaster to cboAccountSelect

 

[MackMan]

Awesome.. this works! It populates the combobox no problem.
However (you didn't know this was coming..)

cboAccountSelect has two columns.... the first is the ID (hidden) and the second contains the name... how can I bind the openargs to column 1 (given zero is the accountID)?

or is that even possible?

 

[CJ_London]

this

cboAccountSelect=me.openargs

assigns me.openargs to the bound column

so assuming the recordsource in frmAccountSummary includes AccountID (you don't need to display it) then change the dblclick event to

 docmd.OpenForm "frmMain",,,,,,[COLOR=red]accountID[/COLOR]

 

[MackMan]

sadly not as it's a textbox

 

[CJ_London]

sadly not as it's a textbox

WHAT is a textbox?:banghead:

 

[MackMan]

Bingo! :banghead:

I just needed to add the Account ID field to the mainform and hide it. Then used the AccountID in the open args in the other form.

All works perfectly.

Thank you and sorry!

but It's really is appreciated.

 

[CJ_London]

glad you got there in the end.

For the future, be consistent in what you call things and learn the correct terminology. Understand the difference between controls and fields, subform controls and subform sourceobjects

Appreciate it is difficult for someone new to access to know these things but it will come with time.