How to transfer specific records from Table_1 to Table_2?

[Pampas58]

Here’s my problem, and my forehead is bleeding from both ignorance (which I hope is curable) and from pounding my forehead against the metaphorical wall.

I have a new project that requires the following:

1) When a shop calls in asking for a replacement unit, I need to generate an open order which happens in the ORDER STATUS table.

2) When I have a repaired unit available (typically in stock) I ship it, and record the shipment to the store, and mark the record (via a status field) Awaiting Core, in the ORDER STATUS table.

3) When the store sends me a unit back, I match it with the last Order for that store with a status of Awaiting Core unit from the ORDER STATUS table and now I need to move a copy of this return to my REPAIR LOG table.

Note that the repair log has a number of additional functions that cover the material cost, the labor cost, and selling price. To keep track of the material cost I have a One-to-Many relationship with the parts I’m using.

The problem is, and my ignorance is the cause of the problem, that I want the user to only make one entry of the returned unit in the ORDER STATUS table. From there I want to take the Order_ID field (tracks the open orders with an AutoNumber), the Store_No field, and the Return_Date fields. I want to take the three fields, and put them in the table called REPAIR LOG.

Then, I want to grab the REPAIR LOG Repair_ID field (Autonumber that will be my Work Order for the repair) and put it as a reference number in my ORDER STATUS table.

I cannot figure out how to accomplish this; anyone help me?

Thanks a bunch,

Paul

 

[HarleyQuinn]

What have you been thinking so far?

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[Pampas58]

Since I posted the question I found what I was doing wrong with the DAO statements, but still this just "Sort of" works...

Private Sub cmdIssueWorkOrder_Click()
On Error GoTo Err_cmdIssueWorkOrder_Click

'This module transfers information from the tblOrderStatus to
'the table Repair Log
Dim iOrdID As Integer
Dim sStore As String
Dim dDefResDate As Date
Dim sDefSerNo As String
Dim sWorkOrder As String


'First I need to grab the pertinent information from the open form
'before I loose it

iOrdID = [ID]
sStore = [Store No]
dDefResDate = [Defective Received Date]
sDefSerNo = [Defective Serial Number]
sWorkOrder = "CL-" & iOrdID

MsgBox iOrdID & "|" & sStore & "|" & dDefResDate & "|" & sDefSerNo & "|" & sWorkOrder
'this requires that I have Microsoft DAO 3.6 library selected
'in the Tools --> References while editing code.

'this tells the system how to deal with the db = database and the rst = recordset

Dim db As DAO.Database
Dim rst As DAO.Recordset

'This sets db = to the current database and don't forget the () at the end
Set db = CurrentDb()
'this sets the recordset(aka the table) to what you want to use
Set rst = db.OpenRecordset("Repair Log", dbOpenDynaset)

'now I begin to work with the record set
With rst 'Using the Recordset
.AddNew 'Setting up to add a New Record
'.Edit 'Setting up to Edit

![Store No] = sStore
![Serial No] = sDefSerNo
![Date Returned] = dDefResDate
![Work Ord] = sWorkOrder
![Order_ID] = iOrdID




.Update 'Don't forget to update or you loose it


End With

Exit_cmdIssueWorkOrder_Click:
Exit Sub

Err_cmdIssueWorkOrder_Click:
MsgBox Err.Description
Resume Exit_cmdIssueWorkOrder_Click

End Sub

 

[ck1999]

What would happen if you would put a

vrepairlogid = rst("repairid") before the end with

and then after the end with

Set rst = db.OpenRecordset("Order Status", dbOpenDynaset)

'now I begin to work with the record set
With rst 'Using the Recordset
.AddNew 'Setting up to add a New Record
'.Edit 'Setting up to Edit

![refnum] = vrepairlogid


.Update 'Don't forget to update or you loose it

End With

you will have to change field names and table names I just wanted to give you an idea.

ck1999

 

[ck1999]

set rst = db.OpenRecordset("Order Status", dbOpenDynaset)

'now I begin to work with the record set
With rst 'Using the Recordset
.AddNew 'Setting up to add a New Record
'.Edit 'Setting up to Edit

![refnum] = vrepairlogid


.Update 'Don't forget to update or you loose it

This should use a rst.findfirst and then reference a field instead of using .addnew


ck1999

 

[Pampas58]

ck199

OK, third time reading what you were suggesting and I think it hit me; finally! Is what you're suggesting that once I have the record found - the match from the first Do While Not.eof that finds the record I'm looking, then caputre the record number and go right back to it?

Or am I still missing something critical in here?

Pardon my ignorance, but I think you're trying to tell me something important here, and I'm missing it.

Thanks for you help,

Pampas58.

 

[ck1999]

Ok Let me try an explain what I am thinking and you determine if my logic and your need are the same


You code as presented will add an entry into the repair log. I am assuming there is already an entry in the order status table.

3) When the store sends me a unit back, I match it with the last Order for that store with a status of Awaiting Core unit from the ORDER STATUS table and now I need to move a copy of this return to my REPAIR LOG table.

Now you want to

Then, I want to grab the REPAIR LOG Repair_ID field (Autonumber that will be my Work Order for the repair) and put it as a reference number in my ORDER STATUS table.

You should be able to since you just created a record using your code in the repair log. to set a variable equal to teh repair_ID Field.

vrepairlogID = rs("repair_ID") this may need to be done after the update! Use f8 to step through and check the value of vrepairlogID to make sure it is correct.

Then you have to update order status table with the repair log id so
use

[code[
set rst = db.OpenRecordset("Order Status", dbOpenDynaset,)
strCriteria = "[Order_ID] =" & iOrdID

rst.FindFirst (strCriteria)


'now I begin to work with the record set
With rst 'Using the Recordset
.Edit 'Setting up to Edit
[refnum] = vrepairlogid 'refnum is your reference field in the orderstatus table
.Update
end with
[/code]

This code will update the Order Status Table with the Repair Log ID #

Hope this better explains it and works for you

You could also I think use a sql or where clause for the recordset instead of using findfirst.

ck1999