I have a form with several text controls, as well as a subform on the bottom that is a datasheet of a table.

What I'd like to do is add in a Macro or some VBA that can do the following:

1. The user clicks on a record in the subform (could be just [P/N], or anywhere in the record).

2. Depending on the record they clicked, the [P/N] from that record would be sent to the [P/N] control on the main form.

3. If the user clicks a new record in the subform, the [P/N] is refreshed.


Any ideas?

Thanks,

Edited by: Nightsyte on Fri Aug 8 8:33:42 EDT 2008.

In the subform's OnCurrent event put code like this:

---------------
Private Sub Form_Current()
Forms("MainForm").TextBox.Value = PartNumberTextBox.Value
End Sub
---------------

Alright, here's what I ended up with:

-------------------
Forms("Request Inventory").[Staples P/N].Value = Me.[Staples P/N].Value
-------------------

Request Inventory is the Main form.
Staples P/N is the name of the Main Form's text box, as well as the name of the control in the subform.

The above code gives me:

Runtime '2448' - You can't assign a value to this object.

Thanks for your help so far.


Also, another question: If this subform is being used on two mainforms and I want the same function to work on both forms, can I simply add a second line like this:

Forms("Request Inventory").....
Forms("Restock Inventory").....

?

P.S. - This forms will never be both open at the same time, if that affects anything.

Does your "[Staples P/N]" textbox on the main form have its ControlSource property set? You should be passing this value to an unbound textbox.

If you wanted to use this on two forms, you would have to check whether each form was open before referencing it...

---------------
If CurrentProject.AllForms("Request Inventory").IsLoaded Then
Forms("Request Inventory").[Staples P/N].Value = Me.[Staples P/N].Value
Else
If CurrentProject.AllForms("Restock Inventory").IsLoaded Then
Forms("Restock Inventory").[Staples P/N].Value = Me.[Staples P/N].Value
end if
end if
---------------

Alright, that worked perfectly, but left me we a new and probably easier problem.

Now that the mainform text box is unbound, even when the number is filled in it doesn't translate back to the table anymore.

Is there a way to fix this, without having to rebind the control? Feel free to call me a n00b on this one. =)

Maybe easier...it all depends. I guess I need to know more about what you are actually doing to answer this question.

Ok.

Request Inventory and Restock Inventory are two different forms, both bound record-for-record to a table called Transactions.

(Note: Standard users of these forms will never see them except in "New Record" mode)

The user enters a bunch of other information on the form, selects the item (filling in the Staples P/N), enters a quantity, and presses a submit button (which is a glorified "new record" button).

Really the only difference between Request Inventory and Restock Inventory, is that the "Quantity" controls are bound to different columns in the Transactions table. Request goes to a "Quantity Requested" field, while Restock goes to a "Quantity Restocked" field. Depending on which Form filed the record, the unused column is automatically 0.

Then, for stock level tracking purposes, the "current stock" of an item is calculated (at runtime) using the sum of all an item's "Quantity Restocked" minus all an item's "Quantity Requested".

Okay...try this...

Put the ControlSource of the [Staples P/N] textbox back to what ever field it was linking.

Set the Tag property of your subform equal to 0.

Change the code in the OnCurrent event to:
---------------
If me.tag=1 then
If CurrentProject.AllForms("Request Inventory").IsLoaded Then
Forms("Request Inventory").[Staples P/N].Value = Me.[Staples P/N].Value
Else
If CurrentProject.AllForms("Restock Inventory").IsLoaded Then
Forms("Restock Inventory").[Staples P/N].Value = Me.[Staples P/N].Value
end if
end if
else: me.tag=1
end if
---------------

(Deleted)

Did you set the defualt value (in design mode) of the tag property to 0?

It might be easier if you used quotes instead...If me.tag="1" then...Else: Me.tag="1"

Yea, that was my fault. I set the Tag to 0 of the subform, but I did it inside the mainform in the mainform's design mode.

Once i changed it with the subform in its own design mode, worked like a charm.

Thanks a TON merlicky.

No problem.