Please help, i'm really lost....Coding copying fields (1 Viewer)

[JustMaybe]

Okay..got a really strange problem…(or I’ve just been completely dumb!)

I have a form with order details such as customer name, address etc. Within this form I have a subform…this includes details such as products , price etc.
The problem is that each detail (line in the subform for each product) needs to have a License number.

This License number needs to be stored in a table (for reference) with details from both the order form and the orders subform.

To deal with this I created a form which displays all the fields that I need to be in the table. So when you click on a button in the ‘orders’ form this links you to my ‘license form’.
This form displays the items selected in the orders subform (for the particular record you are looking at) this form can be completed then …

(this is probably ridiculous logic!)

I wrote some code to try and copy all the information from relevant fields into my Licensing table. As I wasn’t sure of code I used recordset at the time. (this happens by ‘on click’ of another button). This was the first bit of code I ever wrote in VB

This all (unbelievably) worked for a period of time, until lately…there doesn’t appear to be any logic behind where it isn’t working, random fields don’t copy on some occasions , whereas other fields don’t copy on other occasions.

Heres my code:
Private Sub Command20_Click()

Dim rsLicense As Recordset
Dim rsOrderDetails As Recordset
Dim rsOrders As Recordset
'
Dim sDetailID As String
'
Set rsOrderDetails = Application.CurrentDb.OpenRecordset("order details", dbOpenDynaset, dbPessimistic)
Set rsLicense = Application.CurrentDb.OpenRecordset("License")
Set rsOrders = Application.CurrentDb.OpenRecordset("Orders")
'
rsOrderDetails.MoveLast
rsOrderDetails.MoveFirst
'
sDetailID = Me.DetailID
rsOrderDetails.FindFirst ("DetailID = " & sDetailID)

rsLicense.AddNew
rsLicense("DetailID") = rsOrderDetails("DetailID")
rsLicense("73 Serial No") = rsOrderDetails("SerialNO")
rsLicense("Product Name") = rsOrderDetails("ProductID")
rsLicense("AMP Renewal Date") = rsOrderDetails("RenewalDate")
rsLicense("Current Version/Build No") = rsOrderDetails("Version")
rsLicense("End-User") = rsOrders("EndUser")
rsLicense("Reseller") = rsOrders("Re-Seller")
rsLicense("Location") = rsOrders("ShipCity")
rsLicense.Update
rsOrderDetails.CLOSE
rsLicense.CLOSE
Exit_Command20_Click:
Exit Sub

Err_Command20_Click:
MsgBox Err.Description
Resume Exit_Command20_Click

End Sub

All the fields match with correct names…can anyone please help??????

 

[Jerry Stoner]

I dont understand why you need a new table. Why cant you just use a query to get data from both tables when you need it. Im assuming you have a one to many relationship set up between the 2 tables.

 

[JustMaybe]

Basically i need it ...as it will be the table that is opened when a user rings our office needing to work out which license belongs to which location ...the users visual recognition between hundreds of records is needed!!

This is not my choice..i know its not logical.

It is a one-to-one relationship

 

[chrismcbride]

Jerry is correct. You do not need another table. There is no point in storing duplicate information in two or more tables. In fact you run the risk of showing incorrect or incomplete records by doing it this way. There is at least a chance that records in one table will not be correctly added or edited in the second table.
When your users request information, the you should run a parameter query that displays Licences per Location or any other criteria you want.
If you want, you could put this query behind a Report or simply display it.

Chris