Getting values from a subform

[Rob Brookbanks]

I am sure this is a common question, but I can't seem to find the answer!

I have a main form which is bound to an underlying temporary table. This
table stores most of the info I need.

The main form has an unbound subform which has an unbound dropown box that
selects an Account_Ref and a textual company name from an ODBC query. The
Account_Ref field is the bound column in the dropdown box.

How do I get the Account_Ref field that I know is in my subforms dropdown
box into the underlying table of my main bound form?

Please help, it's late and my brain hurts

 

[MacDermott]

Wow! My brain hurts, too - can you help me understand why you would use an
unbound subform?
The simple solution, of course, would be to put your combobox onto the main
form, and set its ControlSource to the appropriate field in the main form's
recordset.

HTH
- Turtle

 

[John Vinson]

How do I get the Account_Ref field that I know is in my subforms dropdown
box into the underlying table of my main bound form?

I agree with Turtle - this seems like a VERY wierd way to do things!

But to answer the question as posted, use the AfterUpdate event of the
combo box:

Private Sub combobox_AfterUpdate()
Parent!AccountRef = Me!comboboxname
End Sub

 

[Rob Brookbanks]

The reason why, and bear in mind I am probably going about this all wrong is
that I am booking items in from a purchase order.

The most efficient way of booking things in is Select Purchase order >
Select Customer > Add items.

Usually all items are for the same customer with maybe one exception once in
a while.

Now I can set the control source of the combo box to the underlying
temporary table field and it puts the data in the Account_Ref field in just
as you would expect. But, the next thing I do is a requery to refresh a
different subform that builds a list of the items being booked in.
(Effectively, you can see a list of items you are going to add to the main
table before you commit.)

Perhaps a better way of doing it would be to set the control source of the
combo box to the Account_Ref field in the table but then "Carry" the value
in the Account_Ref combo box onto the next record, otherwise you have to
select the customer again.

Problem is, I don't know how to do this. Maybe a better question would be

"How do I carry the value that is in a combo box from record to record?"
Can I just copy the bound columns value somehow and then stick it back
afterwards?

I control the requery using a "Next" button so I could grab the value of the
Account_Ref combo box in the macro that drives the event just before the
update and then paste it back at the end of the macro.

Using that method, your default value would be the value from the previously
booked in item, but it could be changed from the combo box.

I know I am probably offending you Access purists, but my app nearly works
and does exactly what I want, will save the stores about two hours a
day........ and it looks real nice!

 

[MacDermott]

Sorry if I'm coming across as a "purist" -
I'm a great believer in using what works.
One of the reasons I like Access so much is that it's flexible enough
that a lot of different approaches can work.

That said, I've also seen a great many applications which were "just almost
finished", but what the author perceived as a small final problem turned out
to be the result of something like poor table design at the outset.

Now, I'm no expert in accounting, but it sounds pretty odd to me to have a
single purchase order with more than one customer. Even if this happens
rarely, it must be allowed for if it happens at all.

Taking your word for it, however, that you really need to store the customer
on each detail record, because it can change within a purchase order, you
might consider adding code like this to the (bound) combo-box's AfterUpdate
event procedure:
MyCombo.DefaultValue=MyCombo.Value
This way, whenever the value in MyCombo (of course you'll use the name of
your own combobox) changes, its default value will be updated. The next
record you enter will automatically be filled with that value, but of course
you can make another change if you like. That value will then in turn
become the new default.

HTH
- Turtle

 

[Rob Brookbanks]

I didn't necessarily mean you were a purist, just that they are out
there....... Now is that even more of an insult? ;-)

Mind you, that worked a treat.... well, actually it didn't quite, but that
may have something to do with how much refreshing goes on on my form at
every "Next" click!!!

But you got me heading in exactly the right direction. All I had to do in
the end was put a hidden unbound textbox on the form and when I click next
it now goes

MyComboTextBoxHolder.Value = MyCombo.Value
DoCmd.GoToRecord , , acNewRec
MyCombo.Value = MyComboTextBoxHolder.Value

Genius, does exactly what I want! Thanks very much for the reply, that
little gem will get me through all sorts of tricky spots in the future!