copy data and / or create a new record

[Adam858]

Hi all,

I am trying to create a button on a form in my db. I have a form called Jobs and a form called customer orders.

I want each job to have one invoice (invoices are created using the customer orders form).

The button is on the jobs form. When clicked I want it to create a new record on the customer orders form and enter the job id and customer id from the jobs form into the customer orders form. Once a record is created I need the same button to just open the relevant record based on the job id and customer id. Each job can only have one invoice, and I have set the one to one relationship in the underlying tables.

My visual basic knowledge is very limited, I have so far managed to make the button open and filter the form by customer id and job id (by stealing code from the access wizzard):

Private Sub invoice_Click()
On Error GoTo Err_invoice_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "CustomerOrders"

stLinkCriteria = "[CustomerID]=" & Me![CustomerID]
stLinkCriteria = "[JobID]=" & Me![JobID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_invoice_Click:
Exit Sub

Err_invoice_Click:
MsgBox Err.Description
Resume Exit_invoice_Click

End Sub

So this code is ok if the record is already created, but on a new job, I want to create a new record with the customer orders form already containing the customer id and job id from the jobs form.

Thanks in advance for any help you can offer,

Adam

 

[ted.martin]

Not quite sure exactly what you need but it seems that you want a New Job Record to be created from your Customer Form.

All you need is a button on your Customer form that will open the Jobs form at a new record. e.g. Do.cmd.OpenForm,"F-Jobs",acNormal, , , acFormAdd,,CustomerNo.value

Usually the JobID would come directly from the from the AutoNumber on the Job table.

To carry through the Customer Number, when the Job form opens you need the following line of code on the OnOpen event


If me.newrecord = true then
CustomerNumber.setfocus
CustomerNumber.text = me.openargs
someotherfield.setfocus
End if

 

[Adam858]

Thanks for your help Ted, I've probably explained it really badly, my explanations are about as good as my programming

I have decided to simplify what I want into 2 different buttons for now, and I will try to combine the code later when I get it all to work, so for now, button 1:

I have a Jobs form that a technician uses to look up details of a job they are working on. This form already contains a JobID and CustomerID. I want a button that the technician will click to do the following:

1 Create a new invoice using the Customer Orders form. (this I can manage lol).

2. From the Jobs form I want to take the values from CustomerID and JobID and insert them into the new record on the Customer Orders form. (this is the bit I am stuck on atm).

Thanks again!

Adam

 

[ted.martin]

Ok - the simplest and perhaps not clasically the best way is to use a couple of Public Variables. Declare them in a module and then before the button event, just use this code:

myVar1 = CustomerID.value
myVar2 = JobID.value

When the form new record form opens (or Load) event, then complete the required fields using the code the reverse way around.

 

[Adam858]

Thanks I will give it a try!