Availability of Control Value when updated from another form

[Guest]

I am tring to display data on a second form based on a selected value in the
first form.
Open FORM works fine (this code is run from the first form):
DoCmd.OpenForm "Form2", acNormal
[Forms]![Form2].TextBox2.Value = "100"

TextBox2 value in used in a WHERE clause (within a command button on the
second form (form2) that also works fine when clicked).

The problem is, TextBox2 value is NULL (or default value) up until the
form is displayed so the code that uses this value returns no records.
(Unless I click on the button after form is displayed).

OPEN, LOAD, RESIZE, ACTIVATE, CURRENT all retun NULL. (Enter and GotFocus
events don't execute)

Does any one know at which FORM's event, value of a Control, which has
been updated from another FORM, becomes available? So, I can run code
against it!

Thank you in advance.
M.Afzal

 

[Guest]

Hi Afzal,

Quite an interesting problem...

I do this all the time and write code such as the snippet below that checks
a list box for selected items and then opens a form for each one. This
particular example was done through a double click event of a listbox
control. The 'MapHeaderIdentifiier' is textbox control on my second form.

Your code looks fine except for it appears to be missing a space after the
acNormal and filter.

For Each varItem In lstDataMapSummary.ItemsSelected
selectedItem = lstDataMapSummary.ItemData(varItem)
DoCmd.OpenForm "frmDataMapDetail", acNormal, , "MapHeaderIdentifier=
" & selectedItem
Next varItem

Where does the code break exactly if you step through it?

Lance

 

[Guest]

Your code would be fine if the target form was bound to a table or query. My
form is not. Than is why I am just opening it and placing the value in a
text box on the target form.
I did not bind my target form to a table or query on purpose because if
there was no records found, the target form, would be blank and user would
have to close it and come back to the first form to make a different choice.

Thank you,
M.Afzal

Hi Afzal,

Quite an interesting problem...

I do this all the time and write code such as the snippet below that checks
a list box for selected items and then opens a form for each one. This
particular example was done through a double click event of a listbox
control. The 'MapHeaderIdentifiier' is textbox control on my second form.

Your code looks fine except for it appears to be missing a space after the
acNormal and filter.

For Each varItem In lstDataMapSummary.ItemsSelected
selectedItem = lstDataMapSummary.ItemData(varItem)
DoCmd.OpenForm "frmDataMapDetail", acNormal, , "MapHeaderIdentifier=
" & selectedItem
Next varItem

Where does the code break exactly if you step through it?

Lance

I am tring to display data on a second form based on a selected value in the
first form.
Open FORM works fine (this code is run from the first form):
DoCmd.OpenForm "Form2", acNormal
[Forms]![Form2].TextBox2.Value = "100"

TextBox2 value in used in a WHERE clause (within a command button on the
second form (form2) that also works fine when clicked).

The problem is, TextBox2 value is NULL (or default value) up until the
form is displayed so the code that uses this value returns no records.
(Unless I click on the button after form is displayed).

OPEN, LOAD, RESIZE, ACTIVATE, CURRENT all retun NULL. (Enter and GotFocus
events don't execute)

Does any one know at which FORM's event, value of a Control, which has
been updated from another FORM, becomes available? So, I can run code
against it!

Thank you in advance.
M.Afzal

 

[Guest]

Fair enough reason to avoid binding, but if you are in a hurry, here is a
suggestion.

Bind the form to a table or query like usual but prior to opening the form,
do a record count on whatever resultset would be displayed. If the record
count is equal to zero, display a message box, else continue with the
DoCmd.OpenForm with a filter applied. I would imagine that you would perform
some form of a check like this prior to opening a detail form anyways since
it wouldn't matter if the recordsource is a query or direct binding to a
table.

Where is the rest of the code then that indicates how you are trying to
populate the form? Perhaps if you showed a bit more we could nail this thing
for you.

Lance

Your code would be fine if the target form was bound to a table or query. My
form is not. Than is why I am just opening it and placing the value in a
text box on the target form.
I did not bind my target form to a table or query on purpose because if
there was no records found, the target form, would be blank and user would
have to close it and come back to the first form to make a different choice.

Thank you,
M.Afzal

Hi Afzal,

Quite an interesting problem...

I do this all the time and write code such as the snippet below that checks
a list box for selected items and then opens a form for each one. This
particular example was done through a double click event of a listbox
control. The 'MapHeaderIdentifiier' is textbox control on my second form.

Your code looks fine except for it appears to be missing a space after the
acNormal and filter.

For Each varItem In lstDataMapSummary.ItemsSelected
selectedItem = lstDataMapSummary.ItemData(varItem)
DoCmd.OpenForm "frmDataMapDetail", acNormal, , "MapHeaderIdentifier=
" & selectedItem
Next varItem

Where does the code break exactly if you step through it?

Lance

I am tring to display data on a second form based on a selected value in the
first form.
Open FORM works fine (this code is run from the first form):
DoCmd.OpenForm "Form2", acNormal
[Forms]![Form2].TextBox2.Value = "100"

TextBox2 value in used in a WHERE clause (within a command button on the
second form (form2) that also works fine when clicked).

The problem is, TextBox2 value is NULL (or default value) up until the
form is displayed so the code that uses this value returns no records.
(Unless I click on the button after form is displayed).

OPEN, LOAD, RESIZE, ACTIVATE, CURRENT all retun NULL. (Enter and GotFocus
events don't execute)

Does any one know at which FORM's event, value of a Control, which has
been updated from another FORM, becomes available? So, I can run code
against it!

Thank you in advance.
M.Afzal

 

[Marshall Barton]

I am tring to display data on a second form based on a selected value in the
first form.
Open FORM works fine (this code is run from the first form):
DoCmd.OpenForm "Form2", acNormal
[Forms]![Form2].TextBox2.Value = "100"

TextBox2 value in used in a WHERE clause (within a command button on the
second form (form2) that also works fine when clicked).

The problem is, TextBox2 value is NULL (or default value) up until the
form is displayed so the code that uses this value returns no records.
(Unless I click on the button after form is displayed).

OPEN, LOAD, RESIZE, ACTIVATE, CURRENT all retun NULL. (Enter and GotFocus
events don't execute)

Does any one know at which FORM's event, value of a Control, which has
been updated from another FORM, becomes available? So, I can run code
against it!

Typically, you can not assign a value untill the Load event,
which may not have started by the time the line of code
after the OpenForm returns.

You might try adding one or two DoEvents before assign a
value to textbox2. But, this is an iffy approach to
dealling with timing issues.

There are two other approaches that are guaranteed to deal
with your need. One is to change the code in form2 to
retrieve the value from a (hidden?) control on form1.

The other is to pass the value in the OpenForm's OpenArgs
argument to pass the value to Form2. Note that this will
only work if Form2 is **not** open when the OpenForm
executes.

 

[Guest]

Thank you very much. I really appriciate your continued effort.

The rest of the code is run from a command button on the form which executes
vb (ado) code with select statement and where clause that populates customer
detail, another section of code populates list box with order Detail, and
another with Notes history. Code does check for recordCount and display msg
if zero. Without leaving this form, user can select another customer and
view same details or modify them.

So, this is way there is dual (redundent) functionality. User can choose
customer from the first form and click on view detail and come to this
(second) form to view details. And, while on this form, can decide to select
a different customer to view it's details.

Although I am not in a hurry, but, it would be nice to find out how to pass
a value to a form that can actually be used in code before form is fully
loaded/displayed.

Thank You,
M.Afzal

Fair enough reason to avoid binding, but if you are in a hurry, here is a
suggestion.

Bind the form to a table or query like usual but prior to opening the form,
do a record count on whatever resultset would be displayed. If the record
count is equal to zero, display a message box, else continue with the
DoCmd.OpenForm with a filter applied. I would imagine that you would perform
some form of a check like this prior to opening a detail form anyways since
it wouldn't matter if the recordsource is a query or direct binding to a
table.

Where is the rest of the code then that indicates how you are trying to
populate the form? Perhaps if you showed a bit more we could nail this thing
for you.

Lance

Your code would be fine if the target form was bound to a table or query. My
form is not. Than is why I am just opening it and placing the value in a
text box on the target form.
I did not bind my target form to a table or query on purpose because if
there was no records found, the target form, would be blank and user would
have to close it and come back to the first form to make a different choice.

Thank you,
M.Afzal

Hi Afzal,

Quite an interesting problem...

I do this all the time and write code such as the snippet below that checks
a list box for selected items and then opens a form for each one. This
particular example was done through a double click event of a listbox
control. The 'MapHeaderIdentifiier' is textbox control on my second form.

Your code looks fine except for it appears to be missing a space after the
acNormal and filter.

For Each varItem In lstDataMapSummary.ItemsSelected
selectedItem = lstDataMapSummary.ItemData(varItem)
DoCmd.OpenForm "frmDataMapDetail", acNormal, , "MapHeaderIdentifier=
" & selectedItem
Next varItem

Where does the code break exactly if you step through it?

Lance



:

I am tring to display data on a second form based on a selected value in the
first form.
Open FORM works fine (this code is run from the first form):
DoCmd.OpenForm "Form2", acNormal
[Forms]![Form2].TextBox2.Value = "100"

TextBox2 value in used in a WHERE clause (within a command button on the
second form (form2) that also works fine when clicked).

The problem is, TextBox2 value is NULL (or default value) up until the
form is displayed so the code that uses this value returns no records.
(Unless I click on the button after form is displayed).

OPEN, LOAD, RESIZE, ACTIVATE, CURRENT all retun NULL. (Enter and GotFocus
events don't execute)

Does any one know at which FORM's event, value of a Control, which has
been updated from another FORM, becomes available? So, I can run code
against it!

Thank you in advance.
M.Afzal

 

[Guest]

Thanks both of you.
M.Afzal

I am tring to display data on a second form based on a selected value in the
first form.
Open FORM works fine (this code is run from the first form):
DoCmd.OpenForm "Form2", acNormal
[Forms]![Form2].TextBox2.Value = "100"

TextBox2 value in used in a WHERE clause (within a command button on the
second form (form2) that also works fine when clicked).

The problem is, TextBox2 value is NULL (or default value) up until the
form is displayed so the code that uses this value returns no records.
(Unless I click on the button after form is displayed).

OPEN, LOAD, RESIZE, ACTIVATE, CURRENT all retun NULL. (Enter and GotFocus
events don't execute)

Does any one know at which FORM's event, value of a Control, which has
been updated from another FORM, becomes available? So, I can run code
against it!

Typically, you can not assign a value untill the Load event,
which may not have started by the time the line of code
after the OpenForm returns.

You might try adding one or two DoEvents before assign a
value to textbox2. But, this is an iffy approach to
dealling with timing issues.

There are two other approaches that are guaranteed to deal
with your need. One is to change the code in form2 to
retrieve the value from a (hidden?) control on form1.

The other is to pass the value in the OpenForm's OpenArgs
argument to pass the value to Form2. Note that this will
only work if Form2 is **not** open when the OpenForm
executes.