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

Transfer of records from listbox to a subform

P: 10
I am planning to create a form (Issuance) where all the records of the subform will be selected from the listbox. The listbox is not part of the main and subform but will only be visible upon clicking of the "add" button from the subform. The default view of the subform is in continuous form so that all "products" selected in the listbox will go directly to the subform where the fields "quantity" and "price" will be filled up.

What codes do i need to use so that whenever i choose one "product" from the listbox that "product" will automatically transfer to the "Issuance" subform
and the listbox will automatically closed? Hope you could help me guys to come up with the right codes for this. Thank you!
Oct 31 '06 #1
Share this Question
Share on Google+
7 Replies


PEB
Expert 100+
P: 1,418
PEB
Hi

In after update event in your listbox you need to run an append query to your respective table on which is based your subform

Use

Docmd.runsql

to accomplish your append...

more info about your append query when you supply what fields should be appended and where?

:)
Oct 31 '06 #2

MMcCarthy
Expert Mod 10K+
P: 14,534
I am planning to create a form (Issuance) where all the records of the subform will be selected from the listbox. The listbox is not part of the main and subform but will only be visible upon clicking of the "add" button from the subform. The default view of the subform is in continuous form so that all "products" selected in the listbox will go directly to the subform where the fields "quantity" and "price" will be filled up.

What codes do i need to use so that whenever i choose one "product" from the listbox that "product" will automatically transfer to the "Issuance" subform
and the listbox will automatically closed? Hope you could help me guys to come up with the right codes for this. Thank you!
1. Is your listbox hidden on the main form or on a popup form on its own? If it's hidden on the main form what is its name and if it's on its own form what is the form and the listbox name?

2. What is the table or query behind the subform as this is what's updated not the subform itself? What fields are on the subform?

3. What is the RowSource of the listbox? Is it based on a table, if so which table and what columns are in the listbox?

As PEB says you can run an INSERT query to add records but if you want the code I will need answers to the above.
Oct 31 '06 #3

P: 10
mmccarthy thanks for your reply! here are the answers for your querstions:
1. The listbox I'm planning to use is contained in its own form named "issuance_list" and has the listbox named "prod_list". This "issuance_list" form will appear on the screen once the "add" button in the "issuance" form will be clicked by the user otherwise it is hidden on the "issuance" form.

2. The table I'm using in the subform is "issuance_detail". It has the following fields: issuance_id; issuancedtl_id, prod_id, qty_requested, qty_issued, prod_unit_id. This subform is in continuous form so that all products chosen at the listbox (issuance_list) will go directly to the subform where I will fill up the qty requested and issued and the unit of each product.

3. The RowSource(issuance_qty) of listbox is based on a query and has the following columns: prod_id, product name, and balance.
Nov 2 '06 #4

MMcCarthy
Expert Mod 10K+
P: 14,534
mmccarthy thanks for your reply! here are the answers for your querstions:
1. The listbox I'm planning to use is contained in its own form named "issuance_list" and has the listbox named "prod_list". This "issuance_list" form will appear on the screen once the "add" button in the "issuance" form will be clicked by the user otherwise it is hidden on the "issuance" form.

Firstly, sorry for the delay in replying. I've had the flu.

In the AfterUpdate or OnClick event of the listbox put the following code:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub prod_list_AfterUpdate()
  3.  
  4. Forms![issuance]![issuance_detail].Form![prod_id] = Me.prod_list
  5. Forms![issuance]![issuance_detail].Form![product name] = Me.prod_list.Column(1)
  6. Forms![issuance]![issuance_detail].Form![balance] = Me.prod_list.Column(2)
  7.  
  8. Forms![issuance]![issuance_detail].Form.Requery
  9. DoCmd.Close acForm, "issuance_list"
  10.  
  11. End Sub
  12.  
  13.  
If you have any problems with this, please let me know.
Nov 7 '06 #5

P: 10
Thanks again mmccarthy for your help!

I have tried the codes you have given me but it does not recognize the "issuance_detail" when i debug it. Does the "issuance_detail" stands for the subform or to the table? I replaced it "issuance_detail" (table name) by "issuance subform" (subform name) but it did'nt work as well.

Hope you can assist me to solve this problem! Thanks in advance!
Nov 7 '06 #6

MMcCarthy
Expert Mod 10K+
P: 14,534
Thanks again mmccarthy for your help!

I have tried the codes you have given me but it does not recognize the "issuance_detail" when i debug it. Does the "issuance_detail" stands for the subform or to the table? I replaced it "issuance_detail" (table name) by "issuance subform" (subform name) but it did'nt work as well.

Hope you can assist me to solve this problem! Thanks in advance!
Open the main issuance form in design view. Click on the frame of the subform and open the properties window. Under the Other tab check whats in the Name property. It may not be what you think. This is the name that has to go in the code.
Nov 7 '06 #7

P: 10
I got the correct code!!!!

Thank you very much MMCCARTHY!!!!
Nov 8 '06 #8

Post your reply

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