I have created a Company Details form (frmCompanyDetails) which has a button on it to open a second form (frmJobList) which lists existing jobs in the database to that specific customer by their code (CustCode). I obtain the information for the second form using the CustCode as shown below:

Private Sub cmdJobCost_Click()
    DoCmd.OpenForm "frmJobList", acNormal, "", "[CustCode]=[Forms]!_ [frmCompanyDetails]![TextCodeCust]", acEdit, acNormal
End Sub

The form frmJobList has a field on it to show the CustCode.
If the customer in frmCompanyDetails has existing jobs, this execution works perfectly. The frmJobList shows these existing jobs in a list box and the CustCode is shown in the text box.
My problem is that if the customer does NOT have any existing jobs, the CustCode displays '0' instead of their number which IS shown in the frmCompanyDetails. This is a problem because I need it to show the CustCode to allow the option to Add a new job.

Thanks in advance for your help.

Stephen

I would test for any Jobs first and action Add new job if there isn't one.

e.g.
Private Sub cmdJobCost_Click()
    Dim rs, strSQL
    strSQL = Query for Joblist matched to Client Code
    set rs = currentdb.openrecordset(strSQL)
    if rs.Recordcount = 0 then
        'insert action code to Add new job
    end if
    set rs = nothing
    DoCmd.OpenForm "frmJobList", acNormal, "", "[CustCode]=[Forms]!_ [frmCompanyDetails]![TextCodeCust]", acEdit, acNormal
End Sub

HTH Ray


Cheers Ray

Thanks for your help Ray

Unfortunately I get the exact same problem with the Add Job form. If the customer has no existing jobs, the Add Job form opens blank also.

At present if the customer has existing jobs, the Job List form opens with all the details on it, including the CustCode. From the Job List form I have a button which opens the frmJobAdd which in turn opens correctly with the customers details and a sequential Job Number.

All my problems are arising from customers who do not have an existing job in the database. I must admit I am quite new to this, so the solution is probably obvious to someone, just not me.

Thanks again,

Stephen

I would open form frmJobAdd where I have inseted comment 'insert action code to Add new job'

e.g.
    DoCmd.OpenForm "frmJobAdd", acNormal, "", , acAdd, acNormal



Cheers Ray

That's what I tried but the AddJob form opens blank also from the Company Details form. It only seems to pick up the CustCode if there are existing jobs.

Try a new AddFirstJob form set for add new record. In the On Open you can set the Client code for the first new job

Cheers Ray