Multiple rows on an unbound subform (1 Viewer)

[Mark Wild]

I've got a data entry form that contains a sub form to allow users to enter a quote. THe main form contains the "generic" information, whilst the sub form will allow them to add all the items that are being quoted for.

Is it possible to have mulitple rows on an unbound form as I don’t want to write the data to the table until I’ve run checks on the data in the rest of the form.

How would one loop through the lines in order to move them via a recordset?

 

[JKpeus]

I have never done this, but you could try using a GridControl instead of a sub form. GridControls can be set to RecordSets and then written into a table.

 

[Pat Hartman]

Use a bound subform and do your editing in the subform's BeforeUpdate event. If the record fails the edits, you can cancel the update with:

Cancel = True

 

[Mark Wild]

Thanks to both of you for your help.

JKpeus - I couldn't find any reference to GridControl in help, knowledgebase or on this site so went with Pats suggestion.

Pat - I've got it so that the record doesn't write to the table before I've tested everything, but have a problem when the criteria are not met.

I can move the cursor within the row on the sub form (assuming a cancel=true) but cannot move back onto the main form, or any other rows within the sub form.

Clearing the inputs fields is the only way in which I can "reset" the cursor, but I would rather not make the user re-input the info on the sub frm.

Any idea what is causing the lock?

thanks

 

[JKpeus]

Let me see if I'm understanding your original question...

You have a table for your quotes (we'll call it tblQuotes) which you display in your main form.
In that main form you have a subform that is pointing to the tblQuoteLine.
You want to verify the data in the subform before writing it into the tblQuoteLine table?

Is that correct?


Jason

 

[Mark Wild]

hi Jason,

Pretty much correct on your assumptions. The only difference is that neither of the forms were bound (the sub form now is on Pat's recomendation). Instead, they move the records with a recordset.

It is the data on the main form (have they completed supplier address etc) that I need to check first, then ensure they have completed all the fields in the sub form

 

[JKpeus]

I can move the cursor within the row on the sub form (assuming a cancel=true) but cannot move back onto the main form, or any other rows within the sub form.

Are you talking about using the tab key to move through the records? Or are you talking about the RecordSet?

How big is the database? Could you compress it and post it?

 

[Pat Hartman]

I didn't realize that the main form was not bound. You need to bind the main form also. Do your edits for the main form fields in the main form's BeforeUpdate event and cancel the event with Cance = True if any errors are found. You will not be able to enter data into the subform unless there is a valid record sitting on the main form.

 

[Mark Wild]

Hi Pat,

the reason I had my main form unbound was that I don't want any of the details from the main form or either sub form to be written until all data had been input.

I have now got the before_update function working on the main form and the sub form but have one problem and one issue.

1) If the user has input the the quote "main" information but fails to complete the "details", the general information has already been written to the table. Ideally, until all info is completed, I don't want to record anything (or at least have the ability to clear it from the table) as our network regualrlly goes down and I want ot aviod partital records.

2) My form uses a number of combo boxes as well as text boxes. Now I have the After_update on the form, it is running the after_udate code everytime I change the data in the combo. Is there a way around this? (part of my code includes an msgbox to let the user know whythey can't save the record)

Apologies for the questions but I have never used this solutuon before.

Thanks, Mark

 

[Pat Hartman]

You can use forms bound to "holding" tables. Then have a button on the form that says "complete". When the user presses the complete button, you validate the data and copy it from the "holding" tables to the real tables and delete the record from the "holding" tables.

Or, you can work in the real tables but add a column to your parent table that indicates a record is incomplete. When they push the complete button, you can verify the data and set the complete field to complete.

 

[Mark Wild]

Thanks Pat, I've now got it working with the temp table solution.

Cheers,

M

 

[Pat Hartman]

I should have also mentioned that when using the "holding" tables method, you need to be able to identify which user is working on which set of records if the "holding" tables are in a shared database. You also need to be able to pull up partially complete records from the "holding" tables and finally, you need to be able to identify abandoned data and do something with it.