Display a list of previous jobs with same serial number on current job form (2 Viewers)

[andymartin3186]

Hi Everyone
I have a job system which the main table stores information about a current job, in this it stores the serial number of that job item
The main form shows all the data about the current job including the customer and contact. I would like it to have an area where it displays any instances of the job being in previously (for warranty purposes) using the serial number of the current opened job to look up previous instances of this.
I've created a query using JobID, SerialNumber, DateBookedIn as the data I wish to display, however I have no idea what to put as the criteria, I'm assuming it is going to be something similar to Like [Forms]![Job Form]![Serial], if I run this it displays correctly but I want it to display everything but the current job. What would be the correct code for this?
Will I then need to create a subform inside the current form to display this?

 

[Gasman]

You could just have a listbox? It depends on what you want to do with that data?
I tend to use TempVars and not tie a query to any particular form.

 

[theDBguy]

I was going to suggest using a subform with the proper link on Serial and just use the following criteria in your query under the JobID column.

<>Forms!FormName.JobID

PS. You may have to requery the subform in the Current event of your main Form.
PPS. Oh, and if you only want to see the previous jobs, you may also have to add a criteria against the date column and sort it.

 

[GPGeorge]

Hi Everyone
I have a job system which the main table stores information about a current job, in this it stores the serial number of that job item
The main form shows all the data about the current job including the customer and contact. I would like it to have an area where it displays any instances of the job being in previously (for warranty purposes) using the serial number of the current opened job to look up previous instances of this.
I've created a query using JobID, SerialNumber, DateBookedIn as the data I wish to display, however I have no idea what to put as the criteria, I'm assuming it is going to be something similar to Like [Forms]![Job Form]![Serial], if I run this it displays correctly but I want it to display everything but the current job. What would be the correct code for this?
Will I then need to create a subform inside the current form to display this?

Based on your narrative, I would say that this could well be handled with a main form/subform design.

Bind the Main table to the main form. Bind the table with the table tracking previous instances of the job being in for warranty service.

The Link Master Fields/Link Child Fields property of the subform control will control the records shown in the subform so they reflect the job item selected in the main form.

 

[andymartin3186]

I managed to get the subform in fine and set it up as continuous forms
It displayed the current job so I added the following criteria into the query under JobID column

<>[Forms]![Job Form]![JobID]

I created a test job with a matching serial number to an older job but it displays nothing now. Can anyone see the isue

 

[andymartin3186]

Update - if I run the query with the job form open the query runs fine and displays the previous job, just the subform isn't working correctly for some reason?

 

[Gasman]

Requery the subform? or set it's source again?.

 

[andymartin3186]

Still empty, where do I put the code to requery the subform? on the main form load?
Is strange how I run the query with the form open and it displays the data, its just not showing in the subform

 

[andymartin3186]

Still empty, where do I put the code to requery the subform? on the main form load?
Is strange how I run the query with the form open and it displays the data, its just not showing in the subform

If I run the subform with the job form open it also displays data, its just not displaying data in the subform in the main form. All the record sources are correct

 

[andymartin3186]

Got it, I was using Job ID to link instead of Serial, changed the master and child and it works!
Thanks

 

[Minty]

I used to do this for previous repairs - a small list box with the previous job numbers and dates, most recent first.
You could select any previous repair and see a pop up of the main repair data without leaving the current job.

 

[andymartin3186]

I used to do this for previous repairs - a small list box with the previous job numbers and dates, most recent first.
You could select any previous repair and see a pop up of the main repair data without leaving the current job.

Sounds good. Is the popup a seperate form with critical info?

 

[Minty]

Yes - that's how it was set up, just a snapshot of when, what, and parts fitted.

 

[andymartin3186]

Cheers for that, just knocked one up works a treat