Add record/ transfer PK value from Listbox to subform(datasheet).

[stonward]

I select a product on a listbox that includes the Primary Key. I want to open another form, once a product is selected, with the subform of the newly opened form displaying the selected product details. I have tried all manner of 'solutions' but nothing has worked properly. This is what I am currently using - a simple piece of add record code. Note that if I manually insert the product ID then the rest of the data follows...

Option Explicit

'Add new product to sale
Dim rs As DAO.Recordset
Dim strwhere As Variant
strwhere = Me.List55.Column(0)


Set rs = Form_frm_quotation.frm_quotationSubfrm.Form.RecordsetClone
rs.AddNew
        rs!ProductID = strwhere
rs.Update
DoCmd.OpenForm "frm_quotation"

I have tried all manner of ways to 'force' the PK (productID) into the subform, and although I managed to do it once, it was SUCH a mess I carried on trying other methods.

To explain; in Northwind (for example), on the Orders Form, the user selects the product on the Order Form from a combo box - I can't use this solution because i have too many diverse products, so I need to be able to select them 'elsewhere' ...In order to be able to add more than one product to each order, the product details must show in a subform (the 'many' side). Am I being clear? Please help before I go finally nuts! Thanks guys, (Please note that the Variant datatype is merely me trying (apparently) everything!

Ston.

[stonward]

Please excuse faulty tags....

[Jim Doherty]

Hi Stonward :)

I am not sure I understand the thought process here fully given the RecordsetClone is read only. I get the principle of what you are doing and there are different ways to do it. It is a matter of design I accept whether one uses early binding/late binding or bound/unbound forms and inserting records using VBA addnew or SQL insert statements.

Anyway to have a stab at your question.... having looked at your posted code I summize you are wanting to merely add a record to an underlying table based on the contents of a listbox (of course there is a magnitude of error handling and validation one could get into here but I will let you deal with that)

I have knocked up a simple databse for you to have a look at that tends to follow your naming convention (although I am sure not in every area). There is the VBA AddNew method included (although I prefer myself one line SQL inserts with error handling taboot) Notice the defined SQL recordset I am calling a blank one row namely not calling up the entire table recordset just to add a record.

I have purposefully not been too graceful,effici ent or clean with this code behind the forms in favour of you getting the IF validation logic sequences in there when considering the data you intend to throw about. (notice the counting of existing records so as not to duplicate etc)

Anyway hope it helps you some :)

[NeoPa]

It's not very clear what you're trying to do. Can you explain your situation in real-world terms? I noticed you appear to be trying to add a record to a form's recordset, which is a little weird.

For starters, data can be passed to a form that is opened in code by using the OpenArgs parameter. A form can also be told to deal exclusively with a certain item, or set of items, with the WhereCondition parameter.

I expect your solution lies somewhere along these lines, but without understanding what you're attempting to do clearly enough it's hard to know which.

PS. No worries about the tags. You made the effort and that's the important thing.

[stonward]

Thanks for your help, guys.

It is true that I don't fully grasp the use of recordset clones etc....din't realize they were read only for example, but this is merely the last of a long line of solutions I have tried.
NeoPa, if you look back at some of my earlier posts you will see I've been fighting this for some time: in my previous posts I showed how i was using the wherecondition/openargs method.
Let me try and explain again:

I have a listbox on a form that shows the results of a search for products....I need to be able to select a product and for the details of that product to transfer to a subform control (datasheet) - but this subform is attached to a different main form from the search form. Please help me retrieve my sanity?!

[stonward]

To Jim Doherty,
Thanks for your time: I've just looked at the db you attached which shows my problem....If a system has a relatively small number of products/records, then they could be selected in the manner shown in your example. But my system has over 2500 products, growing by about 10 per day and each of relatively complicated details. So I want to be able to search/select them on a form designed for that purpose - which I have built.
Now, when the system only needed to cater for one product at a time it was easy to open a sales form with the product PK on the main form (along with Invoice PK), and the product details on a subform.
But now I need to cater for any number of products per sale/quote.
How do I transfer my selected product to my sales subform?
Thanks again for your time.
Ston.

[NeoPa]

Stonward,

I tried a reply, but I wasn't able to make it sound anything less than a moan at your response. Please put more effort into making your situation clearer. Read what I have written, and make sure your response explains exactly what you're trying to do, as database work cannot be productive with such general, non-specific, terminology.

That is, of course, if you feel you would like my continued involvement, which is absolutely your choice at this time.

You may feel it a good idea to have a look first at the suggestions I've already given. A better understanding of them may help you to understand why I still don't have enough information to work with.

[stonward]

Sorry NeoPa - Please be assured that I always value your input. Obviously I'm overlooking some basic error in my design coupled with an inability to clearly state what it is I'm trying to do. Naturally I do not expect, nor wish anyone else to solve these issues completely, but perhaps I could ask a more general question in this case?
Firstly I'm going to go back to the beginning whilst looking over all suggestions made so far, although I think the main problem seems to be that I'm not getting across what it is I'm trying to do. 'I'll be back'! Thanks again for your time - it really is appreciated.
Stonward

[Jim Doherty]

To Jim Doherty,
Thanks for your time: I've just looked at the db you attached which shows my problem....If a system has a relatively small number of products/records, then they could be selected in the manner shown in your example. But my system has over 2500 products, growing by about 10 per day and each of relatively complicated details. So I want to be able to search/select them on a form designed for that purpose - which I have built.
Now, when the system only needed to cater for one product at a time it was easy to open a sales form with the product PK on the main form (along with Invoice PK), and the product details on a subform.
But now I need to cater for any number of products per sale/quote.
How do I transfer my selected product to my sales subform?
Thanks again for your time.
Ston.

Hello Stonward

The code I did for you does just that. The main thrust of your original code was to take the value of a listbox column and insert it into a table which was the aim of your original posted code, from what I could see. All I did was merely get it working for you in a format similar to that of your posted code.

The subform on that example database form is requeried to reflect any appends to the underlying table data as you add each item to it from the listbox.

If all you want is a subform on a completely different form there is no big deal, you simply mount it on another form and requery it on that different form instead ie:

Forms!Completel yDifferentForm. form.requery.

The fact that your database is large and ever growing is, with respect,
immaterial to the program flow. It is the passing of the value and the method used that is the critical issue and that is absolutely achievable I can assure you.

I am not able to be more specific than this because I do not see what you have there. I can only guess I am afraid.

[stonward]

Hello Stonward

The code I did for you does just that. The main thrust of your original code was to take the value of a listbox column and insert it into a table which was the aim of your original posted code, from what I could see. All I did was merely get it working for you in a format similar to that of your posted code.

The subform on that example database form is requeried to reflect any appends to the underlying table data as you add each item to it from the listbox.

If all you want is a subform on a completely different form there is no big deal, you simply mount it on another form and requery it on that different form instead ie:

Forms!Completel yDifferentForm. form.requery.

The fact that your database is large and ever growing is, with respect,
immaterial to the program flow. It is the passing of the value and the method used that is the critical issue and that is absolutely achievable I can assure you.

I am not able to be more specific than this because I do not see what you have there. I can only guess I am afraid.

Hi again JimD,

Looking again at your example - I was too stressed with this earlier (broke my mouse!), so had to walk away for a while. What you say makes sense - get back to you asap with details. Thanks again for your time and input.

Ston.

[NeoPa]

Well, it seems Jim has got this covered anyway Ston. Certainly no hard-feelings from me. I let my frustration show at the weekend (a bit like showing shirt-tails in public) but you have nothing to worry about. Your attitude is always exemplary, and expressing problems will always be difficult when the one you're explaining to can neither see what you see, nor easily and quickly respond with clarification questions.

I look forward to any of your future questions :)