Copy query listbox into table

[test2000]

Hi Experts,

I need to copy all variables from a list box and place them into a table, I guess that I need to loop the below? the list box is called Sup_Card_List.

Private Sub CmdCopyCards_Click()
Dim sqlD As String
Dim Value9 As String
Value9 = Me.ComponentID

sqlD = "INSERT INTO tblCardtoComponent(Card_ref,Component_ref)VALUES(" & Me.Sup_Card_List & ", " & Value9 & ")"
CurrentDb.Execute sqlD, dbFailOnError
Call RequeryList2

End Sub

This is the query which populates the list box;

SELECT DISTINCT qryCards.CardtoComponentID, qryCards.Card, qryCards.Card_EOL, qryCards.Description FROM qryCards ORDER BY qryCards.Card;

So for each listbox line I need to copy information into a table.....

any help willl be greatly appreciated

 

[Scooterbug]

You can use the row source of the list box to define what you want to insert.

sqlD = "INSERT INTO tblCardtoComponent(Card_ref,Component_ref) " & _
         "SELECT DISTINCT qryCards.Field1, qryCards.Field2 " & _
         "FROM qryCards"

One question I have is why are you storing duplicate data? This goes against the rules of normalization.

 

[jdraw]

Hi Experts,

I need to copy all variables from a list box and place them into a table, I guess that I need to loop the below? the list box is called Sup_Card_List.

Private Sub CmdCopyCards_Click()
Dim sqlD As String
Dim Value9 As String
Value9 = Me.ComponentID

sqlD = "INSERT INTO tblCardtoComponent(Card_ref,Component_ref)VALUES(" & Me.Sup_Card_List & ", " & Value9 & ")"
CurrentDb.Execute sqlD, dbFailOnError
Call RequeryList2

End Sub

This is the query which populates the list box;

SELECT DISTINCT qryCards.CardtoComponentID, qryCards.Card, qryCards.Card_EOL, qryCards.Description FROM qryCards ORDER BY qryCards.Card;

So for each listbox line I need to copy information into a table.....

any help willl be greatly appreciated

How did these items get into the list box? Were they sourced from another table?

 

[test2000]

thanks for the replies,

Basically the reason that I am planning on storing duplicate data is because I have a Form for entering data, though a lot of data is very similar, so I have a Duplicate Button so that all the data remains the same, though a new record is created with a new KeyID. This works fine, though another table is linked to the Form and a list box is given with that info. Hence I am trying to copy that also.

So I have a form, built from a query which is opened using the below command;
DoCmd.OpenForm "frmIOS_Release", , , , , acDialog, "View"

Then within the form I have a list box with a 'Source Row' of;

SELECT DISTINCT qryCards.CardtoComponentID, qryCards.Card, qryCards.Card_EOL, qryCards.Description FROM qryCards ORDER BY qryCards.Card;

This queries tables which are linked to the tables queried for the form.

So when I open the form I press the duplicate button, then a new record is created with all the fields being copied, though I still have a list box and this information is not copied... so that is what I want to do..

So my plan is to open the form, copy everything that exists in the list box, then press the dublicate button. Then enter the information from the list box into my association table associating it to the new record keyID....

Hope this makes sense!!! - so affectively I am not duplicating data, I am just coping data to a new record to be adjusted slightly

 

[Scooterbug]

thanks for the replies,

Basically the reason that I am planning on storing duplicate data is because I have a Form for entering data, though a lot of data is very similar, so I have a Duplicate Button so that all the data remains the same, though a new record is created with a new KeyID. This works fine, though another table is linked to the Form and a list box is given with that info. Hence I am trying to copy that also.

If you have data that is similar between tables, you should setup a relationship between them. You would store the data that is going to be similar in one table and assign a primary key to that record. Then, when you want to record a new record that relates to the previously stored data you record that Primary key within the record.

For example. Lets say you have a business selling Widgets. You have customer data, order data, and widget data. Now, you want to remember which customer belongs to what orders. The way you are thinking, you want to store the customer data (Name, phone, address, etc) with each order. That violates the rules of normalization. What you would do instead is to record the primary key of the customer record with the Order record.

So my plan is to open the form, copy everything that exists in the list box, then press the dublicate button. Then enter the information from the list box into my association table associating it to the new record keyID....

Hope this makes sense!!! - so affectively I am not duplicating data, I am just coping data to a new record to be adjusted slightly

A couple of things I see wrong here...one is that the row source for the list box has DISTINCT in it, which tells me that your table has duplicate data. Not good. What you should do, if there are several entries in the table that is displaying the data in the list box, is to have a junction table. Something like this:

tblJunction
JunctionID (AutoNumber, Primary Key)
CardToComponentID (Foreign Key)
MainFormID (Foreign Key)

So, you store the ID of the record you are inputing. In addition, you store a single CardToComponentID within the same record. You do this for each CardToComponentID that is required. Now, you can call up all the CardToComponetIDs that are related to the single record by using the MainFormID

 

[test2000]

Thanks a lot for the reply ScooterBug, I have now removed the 'DISTINCT' statement as it is not needed anyway (cut and paste error).

I have a relationship as per below;

#tblComponent..............#tblCardtoComponent............#tblCard_Types
ComponentID -----------Component_ref......................Card
Notes...........................Card_ref----------------------Card_TypesID

*Note that '----' means a relationship link.. '....' is just used to make the columns

So I have a record with key ComponentID, I want to copy all the Card_TypesID's that are associated with that record to another record.... Not sure of the best way of doing it, whether take information from the querie that produces the list box, or just change the tblCardtoComponent table copy line with the old Component_ref, then add the new Component_ref from the new records ComponentID??


I would have thought that it could be something like this, though I get an error when it tries to execute;

I currently want to instert into a table a constant value, with a list of values selected from a table,

sqlC = "INSERT INTO table tblCardtoComponent(Card_ref,Component_ref)SELECT (Card_ref FROM table tblCardtoComponent WHERE Component_ref = CompCarry"),VALUES ('" & Me.ComponentID & "')"
CurrentDb.Execute sqlC, dbFailOnError


So say if I have table tblCardtoComponent with the below 2 columns;
1 2
1 3
1 4
9 6
9 7
9 8

Where '1' is 'CompCarry' and 2,3,4 is Card_ref.... say Me.ComponentID = 5, I want the table to copy that listed against 'CompCarry'... hence the resulting table would look like this;

1 2
1 3
1 4
9 6
9 7
9 8
5 2 <<<<<<<<<< additional lines added to the table
5 3 <<<<<<<<<<
5 4 <<<<<<<<<<

Can this be done with a single INSERT INTO command, as there is a VALUE and a SELECT variable..??

Any help will be appreciated!!