By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
461,938 Members | 720 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 461,938 IT Pros & Developers. It's quick & easy.

How do I add records from a form (listbox) to a separate forms subform?

P: 145
I think this may be my final 'issue' with this project....!

I need to add products to a quotation/point of sale form. My products are listed in a listbox on my main form (frmMain) - when I click Add To Basket I need the product to be added to the subform of the Quote form (frmQuotation).

Additionally, I need for the code to recognise whether it is the FIRST product or not.

What I have at the moment is an Employee No. field on the quotation form that if it is 'notNull' then a record already exists in the subform and therefore the subform gets the focus directly, enabling products to be sent over from the main form...
This is what I have attached to my Add To Basket click event...
Expand|Select|Wrap|Line Numbers
  1.  Dim strProduct As String, varSelected As Variant
  2. Dim empVal
  4. If CurrentProject.AllForms(“frm_quotation”).IsLoaded = False Then
  5. InputBox "Enter your I.D", "GYRuS: Quotation by Employee..:"
  6. If empVal = "" Then Exit Sub
  7. Else:  Form_frm_quotation.StaffNo = empVal
  9. DoCmd.OpenForm "frm_quotation", acNormal, , , acFormAdd
  12. If Me.List55.ItemsSelected.Count = 0 Then MsgBox "Select a Product!", vbCritical + vbOKOnly, "GYRuS: Select a Product"
  13. For Each varSelected In Me!List55.ItemsSelected
  14. strProduct = strProduct & Me!List55.Column(0, varSelected)
  15. Next varSelected
  16. gstrwheretyre = "[productid] in (" & strSelected & ")"
  17. DoCmd.OpenForm FormName:="quotesub", wherecondition:=gstrwheretyre
  18. End If
The 'code' is a mess because I've been pulling it around for hours. Virtually the same code (based on gstrwheretyre public variable) works well, but i can't seem to get it to send record details to the subform.

I know from experience here that you can help, it's just whether I've described it concisely enough?!

Thanks for your help,

May 1 '10 #1
Share this Question
Share on Google+
10 Replies

P: 145
Sorry - please note that the Inputbox is a recent method I tried in order to distinguish between the first product added to the quote, and any following products (where Employee No would obviously not be needed).

Thanks again,

May 1 '10 #2

Expert Mod 15k+
P: 31,770
The problem with your code in this question is that we don't know what the problem is. Does it compile? Does it crash? Does it give a different result from what you expect?

I can't understand the question well. Sometimes I can read between the lines, but there is too little to go on here that makes sense.
May 2 '10 #3

P: 37
Tried locals and debug.print / stepping? Also I didn't see gstrwheretyre get declared? Got Option Explicit on? And is it a subform? Then why do you try to open it?

Edit. I think I see what the problem is. Your double quotes aren't quotes but some other char. At CurrentProject.AllForms("frm1") (I used the correct one in these. Thats why u rlly should force yourself to use Option Explicit. So you know ur vars ;)
May 2 '10 #4

Expert Mod 15k+
P: 31,770
That's a good spot Robjens. It seems you're referring to line #4, as all the other quotes appear to be good. This is why [code] tags are so important.

@Robjens I don't want to be too critical, as you're obviously trying to help, but the rules prohibit the use of text-style abbreviations in posts. They make it harder for everyone to understand what is said. Particularly the non-English speaking members.
May 2 '10 #5

P: 37
Oh my fault sorry. I figured it wouldn't be a problem for like 1 line stuff...guess enough people have problems with English not being their native tongue to start with. I'll keep it in mind and gonna read the forum rules now ;)

May 2 '10 #6

P: 145
Oh dear, what i mess I started here, eh?!

As soon as I'd posted - and continued working on the problem, I realised how badly I'd posted the question, and for all of that I apologise. 'Gstrwheretyre' is one of a number of public variables declared from a module created purely for that purpose.

The basic code I enclosed works great for opening a second form showing the record selected in a listbox on the first form. But I need to be able to add multiple items, so the records need to be (added) to a subform on the secondary form. I've searched both this and other access forums, but to no avail as yet.

So, say I have a point of sale form, with Invoice(PK), CustomerID, EmployeeID and date on the main form and a subform with the Order details on. Rather than the user select products directly on the subform, i need them to be able to select them on a listbox on my main search form, click Add to Basket and have the record added to the POS subform.

Sorry for any confusion.

May 3 '10 #7

Expert Mod 15k+
P: 31,770

Have you read and considered RobJen's comment about the double-quote characters you are using in line #4? Your response doesn't make that clear.
May 3 '10 #8

P: 145
Thanks for you reply NeoPa.
Yes, as you can probably see, what I was trying to do there was perform a slightly different task if the Quote (or Point of Sale) form was already open: if it was open then it already had the main form data (customerID, employeeID etc) entered, and so the 'focus' should go straight to the subform - adding records.
Um, because of the difficulties obvious with this method, i'm currently trying something else: using tabs.
Now I have a main form with OrderID, date etc (the One side) linked to a three page tab, one of which holds the Order Details (the many side.).
But one of my tabs needs to be a subform that holds the results of a search of my products. How do I get selections from my search subform over to my Order Details subform?
The third page deals with payment data.
Can you help with this method? I guess the original way I was trying just is never used, since I can't find anything like it anywhere!
Thanks for your time and patience!
May 3 '10 #9

P: 145
PS: I've just noticed what RobJens was talking about when he said that my quotes weren't quotes - how strange! Don't know what happened there, but irrelevant now - but how well spotted?!
May 3 '10 #10

Expert Mod 15k+
P: 31,770
I'm afraid I'm seeing through a glass darkly. It's hard to separate out the important information from the ongoing clarifications (always the case with such additions - It's not just you). What I'm gleaning though, is that you're having trouble referencing items on the subform from controls (formula) or code on your main form.

Assuming this to be the case may I first link you to Referring to Items on a Sub-Form. I don't recall if this makes clear that tabs (Pages) do not need to be included in any reference. If it is accessible from a form, then it is still accessible in exactly the same way if it is within a tab control.

Let me know if this gets you going. Otherwise I may need some clearer pointing to the issue. Clarifications of previous explanations are invariably less clear than a single, succinct post with the whole story in.
May 4 '10 #11

Post your reply

Sign in to post your reply or Sign up for a free account.