Non consecutive records in report.

[pbaldy]

No problem, Neil. Post back if you get stuck.

 

[neilhoop]

Paul,

I'm having a problem getting my head around the new tables, maybe it's just me.

Dealing with only one table to start with, the new CertAppl. It has three fields, CertNum, ApplNum and LineID

As you previously mentioned it would be a good idea to have records corresponding to each line on the Certificate.

In my case I could have up to four records, all with the same CertNum but different ApplNum.

My problem is that I need to now integrate this with what I've already got but I just can't get my head around how to do it.

I thinking along the lines of updating the CertNum table when the Certificate is updated, but how? Is this the best thing to do?

Any suggestions would be appreciated.


----------
Neil

 

[pbaldy]

Not clear on exactly where you're stuck. I would present the data as a subform/subreport. For the transition, you'd run an append query for each of your 4 existing fields. For the first:

INSERT INTO CertAppl(CertNum, ApplNum and LineID)
SELECT CertNum, [ID Appl 1], 1
FROM ExistingTableName
WHERE [ID Appl 1] Is Not Null

 

[neilhoop]

I'm assuming that the code you have written is the append query and I'll need four of them for my application one to update each of the four Appliances.

I'm obviously missing something simple here because however I type in the code I'm getting compile errors.

----------
Neil

 

[pbaldy]

That would go into the SQL window of a query. Double check the table and field names, since I may have goofed them. Since this would be a one-time thing, I'd just run it, change the appropriate values for the second field, run it, etc.

 

[neilhoop]

I get it now that will load my new table up with the data.

I'd already done that.

I'm thinking along the lines of three scenarios

1. Create a new Certificate. Add the data to the table CertAppl

2. Amend a Certificate. Change the data already present in CertAppl

3. Delete a Certificate. (Should never need to be used) Remove data from CertAppl

This is where I'm stuck. How do I add, change and delete the required data in CertAppl. Or am I thinking this through totally wrong?

----------
Neil

 

[pbaldy]

1 & 2) You have a form for the certificate, a subform for the appliance, bound to that table. Master/child link on the certificate field makes sure the subform always reflects data associated with the certificate on the main form. The subform has a combo to select the appliance. That combo gets its selections from the appliance table (row source) but is bound to the field in CertAppl (control source). When you display a certificate in the main form, you'll be able to add/edit/delete records in the subform.

3) If you have referential integrity set up with cascade deletes, the data in CertAppl will automatically be deleted when the related certificate is deleted. You could also just code it to happen.

 

[neilhoop]

1 & 2) You have a form for the certificate, a subform for the appliance, bound to that table. Master/child link on the certificate field makes sure the subform always reflects data associated with the certificate on the main form. The subform has a combo to select the appliance. That combo gets its selections from the appliance table (row source) but is bound to the field in CertAppl (control source). When you display a certificate in the main form, you'll be able to add/edit/delete records in the subform.

3) If you have referential integrity set up with cascade deletes, the data in CertAppl will automatically be deleted when the related certificate is deleted. You could also just code it to happen.

Done all that now and well on my way to getting the Certificate to print out.

In your experience is it at all possible to have the combo's on my main form?

 

[pbaldy]

The combo to choose the appliance? I suppose, but you'd then have to code something to append the values into that table.

 

[neilhoop]

I've decided that due to a few problems with the inherent restrictions with sub reports on Main reports to create a table with all the fields required to print out a Certificate. When the Certificate is to be printed I'll use a button on the Certificates form to write all the data to the single record and then print out a report based on that data.

Would this do to put the data into the table?

DoCmd.RunSQL "INSERT INTO MyTable (Field1, Field2.....) VALUES (Value1, Value2.....)"

Or is there a better way?

If it is OK, Is there anything I should be aware of or look out for when constructing the SQL statement.

----------
Neil