Populating field in a subform

[Bob Scarborough]

G'day,

I have a form with a number of subforms. One is the Product subform which calls a ProductDescriptionSubform (PDSF) that searches a table which has over 30,000 product line items in it. The PDSF provides a means of searching by txt strings in the database.

What I am trying to do is to populate data in the first search string field automatically when I open the PDSF form. i.e. "Tap" moves to the first line in the PDSF. The good news it that I can get this to work on 1st form open of PDSF. However if I close the PDSF form and reopen at another point in the Order Form it still retains the old field contents (i.e. "Tap"). The only way to get around the problem seems to be to close down the whole form (i.e. page) and reenter same. It always works on the first iteration but not subsequent.

I am sure I have overlooked something but haven't been able to crack same. Any ideas??

 

[WayneRyan]

Bob,

Not really understanding here.

Somewhere you do a form requery. After that you could clear the
fields. Right?

Wayne

 

[WayneRyan]

Bob,

I knew my post was way off.

I see that the first record's description has "tap" in it.

Did you parse that out and put it in the two fields in the
form's OnOpen event?

You could put that code in a function and also call it from
the forms GotFocus event.

I'll wait and see what your post says.

Wayne

 

[Bob Scarborough]

Hi Wayne,

Thanks for looking at my problem. It's probably best if you look at the JPEG Ipreviously submitted.

The screen/form with the Merchant group on it allows me to filter the records by Merchant and also the 3 fields "Find record containing", "And" and "And" end up doing a text string search on my product database using a "Like" string. This basically helps me filter the 30,0000 products down to a few quite quickly.

If I then select the '1/2" Tap Reseater' by clicking it with the mouse, it will replace the word "tap" in the "Product Description" field in the Products form and also populate 'Unit, ' Trade Ex' , 'RRPEx' fields etc.
I then click the minus button to the left of the Product Description and the subform with the Merchant subform disappears.

If I then go to say the line item say 'Mixer Oxygen.....' and click the '+' button to the left the Merchant form opens again but the details in the subform would still say 'Tap'

I want it to populate from the contents of what is in the 'Product Description' field prior to me clicking the '+' symbol.

I would love to send you same but I am using ACCESS 2002 and the program is some 35Mbtes even after compacting.

 

[WayneRyan]

Bob,

When you hit the "+" button on the "Mixer" record, you probably
do something like"

DoCmd.OpenForm "frmMerchant" ...

You have some code to parse the product description to get
the product type (like "Tap"). If you could call that you
could do:

Forms![YourMainForm]![YourSubform].Form![Search1] = YourFunction(Me.ProductDescription)

The same for the other field. (There are two).

Something tells me that it is not that easy.

Alternatively, you could move your logic to the GotFocus event
of the Merchant subform. You could even do a Setfocus on the
OnCurrent event of the Product subform (and then set focus) back.

Wayne

 

[SforSoftware]

Hi,

I agree with Wayne that it might be better to use the GotFocus-event of the Merchant-form. You can refer to fields in the datasheet with 'Me.Parent', ie
- On the Merchant-form you've the field txtSearchstring
- On the PDSF you've the field txtProductDescription
Then use in the GotFocus:
Me!txtSearchstring = Me.Parent!txtProductDescription

Luck,

 

[Bob Scarborough]

Wayne and Bert,

You blokes are fantastic. Thanks for all the hard work.
I had tried this beforehand but had the wrong syntax when trying to populate from the parent form.

I am finding it hard sometimes to see if I should apply the code to a Field in the form using ON_FOCUS event , or the Form itself using the ON_ENTER event. Any hints on when to use what??

Anyway the main thing is that it works!!! Thanks heaps!! I really appreciate the help that you blokes give us less experienced code cutters.



On the seperate but related topic is it possible to keep the Merchant Subform on top as a seperate window whilst it is open?

It's just that as I add more entries to the ORDERS FORM the Merchant subform moves further south on the page. To read same I need to keep using the slider bars on the right to see same.