old info on report

[RyDan46]

Ive created a report using infomation from a subforum in my database. When i want to transfer the information from the subforum i have created a command button which send the info onto the report, which is what i want, BUT when i want a new report and i click the transfer button on the subforum from enough customers data the old infomation is still on there so i end up with the old info on another page and the new on a new page.

How do i clear the old infomation from the previous time so that i dont have someone else info on the new report

thanks in advance

RyDAn

 

[THWatson]

RyDan
What is the code behind your command button?

Tom

 

[RyDan46]

Private Sub cmdpreviewcurrent_Click()
strwhere = "[Customer ID] = " & Customer_jobs.[Customer ID]
strReportName = "Customer_jobs"
DoCmd.OpenReport strReportName, acViewPreview, , strwhere
End Sub

 

[THWatson]

RyDan
See if one of the following strwhere clauses work.

strwhere = "[Customer ID] = Customer_jobs.[Customer ID]"

strwhere = "[Customer ID] = " & [Customer_jobs].[Customer ID] & ""

That presumes that "Customer_jobs" is the name of your FORM. If not substitute the name of your form there.

Tom

 

[RyDan46]

the 2 lines of code work they seperate the different reports i have created but they r on sperate pages which is ok but i want to to be able to go to the report and only have the last report on there with out having any prevoius reports. ( so what im trying to do is have the report as a template or something so its blank when i come to send another customer job details) When i add the infomation from the subforum to the report it stores the info in the customer_job TABLE which i used to create the report with in desgin wizard, would creating the report without the wizard and not using the customer_job TABLE. would that make any differnt

 

[THWatson]

RyDan
You say "the 2 lines of code work." Just checking to make sure you're only using one of the two, not both.

However, maybe we need to go further, and therefore I need you to give me some more information about your setup.

Let me see if I understand correctly what you are trying to do. Am I correct that you want to produce a report for just one customer? And that is the customer you are currently making entries for in the subform?

I note that you have a form and subform. I'm assuming then that the form is based on Customers table, and the subform is based on Customer_jobs table, and two are linked by [Customer ID]. Correct?

Tom

 

[RyDan46]

answer to your questions

yes i only used 1 line of code at one time and they both do the job.

Yes i want to produce a report using the information from the current enteries i have inputed into the subforum. With the code ive used it does that but, when i wont to repeat the process again for another customer, it puts the infomation from the subfroum onto the same report, and then i have that infomation and the information from the last infomation i sent over to the report.

My forum is based on a table called CUSTOMERDATABASE, and ive creat a forum using that. I have also created the subforum from a table called CUSTOMER_JOBS. these are connected using the customer ID

Hope this helps hope to hear from u soon
RYDAN

 

[THWatson]

RyDan
Sorry for being late in replying, but I have been out since late morning until just now.

Okay. First of all check to be sure that the "Customer ID" in CUSTOMERDATABASE is Foreign key in CUSTOMER_JOBS. That's probably how you have it set up, but just to check.

Now the next thing is. We have to be able to differentiate among records in the main form, and you are doing this by the [Customer ID] field. But we also have to be able to differentiate among records in the subform, so that just a specific subform record gets printed.

For example, here is code behind a command button that I have on a form, to print only the data that belongs to a specific ID in the main form, but also only the data from the specific date in the subform.

DoCmd.OpenReport "MyReport", acViewPreview, , "[PatID] = Forms!frmPatInfo!PatID AND [CurDate] = Forms!frmPatInfo.Form!frmVisits!CurDate"

Note that in the WHERE clause the [PatID] comes from the main form and the [CurDate] comes from the subform.

So the main form and the subform have to be referenced properly.

If, by using the example, you can't quite get the syntax correct, send me the DoCmd expression you are using.

Tom

 

[RyDan46]

hi thanks for the reply this is driving me mad lol pissed off as i write this anyway

Question what is the foreign key ? is the the primary key ?
If it is ive just come across a problem with adding another job to the same persons record in the subreport, ive had to take the primary key of the customer id because it was saying that u cant have dublicate fields it seems to have sorted the problem out, hope that hasnt caused any other problems doing this

From what im reading u seem to no what i want which is good
the code u have gave me what do i change for that to work for me ?

Main forum = K D Autos Customers database
Subforum = Customers_jobs
Report = Customers_jobs

Sorry be so thick this database will be the death of me
hope to here from u soon
RyDan

 

[THWatson]

RyDan
Is there any chance you could send a piece of the database to me? You could copy the database (Just close it. Click on it and press <ctrl>+C to copy it, and then <ctrl>+V to paste it. You will end up with "Copy of your database.") Then strip out any records which you consider sensitive data that you don't want anybody else to see, but leave me a few records.

I can't show my actual address on here, because we don't want undesirable stuff infiltrating the site. But if sending the database would work for you, interpret the following and send it. I have high speed so it doesn't matter the file size, unless you want to zip it.

twatson at sentex dot "the last three letters of fishnet"

I can look at it sometime this evening. It's almost 5 p.m. where I am located - near Toronto, Ontario.

Tom

 

[RyDan46]

ok im going to send the database to you. Ive took out most of the customers info just left u with my info and my sisters. The subforum is not how i wont it yet but i will finish it once ive got the print report sorted. Im in the UK just to let u no

If ive read ur repsones right ur address is the 6th line down ? if not ive sent an email to someone else

speak to u soon

 

[THWatson]

Just to finish this thread off, the issue was primarily a matter of establishing better linking between a form and subform.

Tom