run sql or filter without button click (1 Viewer)

Zydeceltico · Apr 22, 2019

Hi all - One of my forms passes an integer-type ID to a second form.

That ID number is "held" in an unbound txtbox on form 2.

The ID number represents an assembly made up of two component parts.

On form 2 there are 3 other textboxes named txtFinalProdName, Component1, and Component2.

I have a query that uses the ID number as criteria and returns a string value for each of the other 3 unbound textboxes.

The thing is - - - I don't want to click a button on form 2 to populate the 3 text boxes.

Currently, when form2 is opened from form1, the ID is successfully passed from form1 to form2 to txtFinalProd_FK.

What I would like to have happen is that the moment the ID value is passed to txtFinalProd_FK that value is used as criteria in a query that automatically runs (On Change?) and places the appropriate strings in the other 3 textboxes.

Suggestions as to what this looks like?

Is it as simple as figuring out the SQL statement and putting it on the Change event of txtFinalProd_FK? If that is the case can somebody give me an example of code that passes a criteria into a SQL statement in code?

Thanks,

Tim

theDBguy · Apr 22, 2019

Hi Tim. Sounds like you should be able to simply Requery or Recalc the form to refresh it's information at the time the parameter value changes.

Micron · Apr 22, 2019

change event only works when typing into a control, so it's of no use for this. If you already have code that works on a button click, move it into the form Load event - as long as we're not talking about subforms here. FWIW, you may not need the textbox at all. You can pass the value as a parameter of the OpenArgs property and reference that in form2 rather than a control. If you need to edit the textbox contents, then I guess you do need it.

isladogs · Apr 22, 2019

As already stated use the Form_Load event. If the values aren't automatically updated then ...

EITHER
If the other 3 text boxes are bound then you need an append or update query (depending on whether you are adding a new record or updating an existing one).
This will be used to set the values for the 3 fields shown in those textboxes. For example an update query would be similar to this:

Code:

UPDATE YourTableName SET FinalProdName = '" & StringValue1 & "', Component1 =  '" & StringValue2 & "', Component2 = '" & StringValue3 & "';

Rename all items to match your values

OR if the other 3 textboxes are unbound then use code similar to this:

Code:

Me.txtFinalProdName = stringValue1
Me.Component1 = stringValue2
Me.Component2 = stringValue3

Micron · Apr 22, 2019

while you gents might have the correct interpretation, to me the question implies the use of the OnChange event the moment the control is populated

the moment the ID value is passed to txtFinalProd_FK that value is used as criteria in a query that automatically runs (On Change?)

There might be no plan to have user interaction with the textbox at all. Just sayin...

Zydeceltico · Apr 22, 2019

Micron said:
while you gents might have the correct interpretation, to me the question implies the use of the OnChange event the moment the control is populated
There might be no plan to have user interaction with the textbox at all. Just sayin...

Yep - no interaction. I want the On Change of txtFinalProd_FK to be the trigger itself - and because of that I have no idea what the underlying code looks like.

theDBguy · Apr 22, 2019

Zydeceltico said:
Yep - no interaction. I want the On Change of txtFinalProd_FK to be the trigger itself - and because of that I have no idea what the underlying code looks like.

How exactly does the value in txtFinalProd_FK get changed?

MajP · Apr 22, 2019

the ID is successfully passed from form1 to form2 to txtFinalProd_FK

Sounds to me like you are over thinking this. If you have code sets the value of txtFinalProd_FK then that same code should requery the form. No need to set the controls value and then try to trap the after update event.

Micron · Apr 22, 2019

Like when I said just pass the value to the opening form? Maybe OpenArgs, maybe not. Same result though. I think the statement that the on change event is useless for the stated goal didn't make much of an impression.

Zydeceltico · Apr 23, 2019

theDBguy said:
How exactly does the value in txtFinalProd_FK get changed?

txtFinalProd_FK gets its value from public variable intFinalProdiID being passed from form 1 to form 2. On Load of form 2 triggers Me.FinalProduct_FK = intFinalProdID.

Then I have the 3 unbound text boxes that need populated: txtFinalProdRef (the actual name of the Final Product), txtComponent1, and txtComponent2 (which are the parts that make up the product.

- - - --

Zydeceltico · Apr 23, 2019

Micron said:
Like when I said just pass the value to the opening form? Maybe OpenArgs, maybe not. Same result though. I think the statement that the on change event is useless for the stated goal didn't make much of an impression.

I'm already using OpenArgs to pass a more important value to a different control on Form 2.

Zydeceltico · Apr 23, 2019

MajP said:
Sounds to me like you are over thinking this. If you have code sets the value of txtFinalProd_FK then that same code should requery the form. No need to set the controls value and then try to trap the after update event.

<sigh> I know you're right.

Quick thought to everybody responding (thanks BTW) - I may have to do this a different way altogether as the results of the query that I wish to run (from my OP) result in a datasheet rather than a single record. I can see where trying to get the first record of the datasheet into txtComponent1 and the second record of the datasheet into txtComponent2 could be a hassle.

I suspect what I should do is have a dialog popup box between Form 1 and Form 2 which makes the user make a selection for which Component or Final Product record is passed to Form 2.

If I do it that way - and considering OpenArgs is already being utilized for a different and more important value, I guess I still have the same situation as the OP.

As real world description as I can get is:

I want to do an inspection, so I open my top-level Inspection form and select the Job Number of the work I want to inspect. The Job Number directly informs what the Final Product is and indirectly what the Components are.

So - I pick a job number on Form1. Then, also, on Form1, I select (via clicking a button) what type of inspection I want to do. This currently opens what I have been calling Form2 where I had been hoping to make a selection of which product or component I am about to inspect.

But now I am thinking that instead of moving directly from Form1 to Form2, it may be a better idea to move from Form1 to a dialog box where I pick the product or component and then carry that selected value over to Form2.

Gasman · Apr 23, 2019

You can pass more than one parameter in OpenArgs.?
Just separate them with a unique character that would not be used in the string, like the "|" character.?

Zydeceltico · Apr 23, 2019

Gasman said:
You can pass more than one parameter in OpenArgs.?
Just separate them with a unique character that would not be used in the string, like the "|" character.?

I did not know that or if I did I forgot. Excellent. That will help.

Do you have a code example of how to parse and place them on what would be my Form2?

Gasman · Apr 23, 2019

No, a quick Google shows this
https://www.fmsinc.com/microsoftaccess/Forms/openargs/index.htm

If more than two then I would probably use the Split function.

HTH

Zydeceltico said:
I did not know that or if I did I forgot. Excellent. That will help.

Do you have a code example of how to parse and place them on what would be my Form2?

Zydeceltico · Apr 23, 2019

Gasman said:
No, a quick Google shows this
https://www.fmsinc.com/microsoftaccess/Forms/openargs/index.htm

If more than two then I would probably use the Split function.

HTH

I think that will help. I'll give it a go and let you know.

Certainly useful regardless.

Thanks!

essaytee · Apr 24, 2019

Just another thought, from Form 2 simply reference Form 1 for your required ID value, this would be done in the On Load event of Form 2. (This assumes that Form 2 is always opened by Form 1)

Zydeceltico · Apr 25, 2019

essaytee said:
Just another thought, from Form 2 simply reference Form 1 for your required ID value, this would be done in the On Load event of Form 2. (This assumes that Form 2 is always opened by Form 1)

That's the challenge. Form2 is not always opened by Form1.

But I think I may have a workaround for that.

Thanks!

run sql or filter without button click (1 Viewer)

Zydeceltico

Registered User.

theDBguy

I’m here to help

Micron

AWF VIP

isladogs

MVP / VIP

Micron

AWF VIP

Zydeceltico

Registered User.

theDBguy

I’m here to help

MajP

You've got your good things, and you've got mine.

Micron

AWF VIP

Zydeceltico

Registered User.

Zydeceltico

Registered User.

Zydeceltico

Registered User.

Gasman

Enthusiastic Amateur

Zydeceltico

Registered User.

Gasman

Enthusiastic Amateur

Zydeceltico

Registered User.

essaytee

Need a good one-liner.

Zydeceltico

Registered User.

Similar threads

Users who are viewing this thread