Writing to a TBL from a Form - Prefilled except 1 field.

[txgeekgirl]

OK - I tried to post this Monday and everyone said I needed to have bound controls. That isn't going to work with a prefilled form.

I have a form that has the following controls predefined based on a selection of a Listbox:

Me.AuditElementTB.Value = CStr(Me.ElementLB)
Me.ElementTB = DLookup("[Element]", "[FindingsElements]", "[AuditItemsID]=" & Me.AuditElementTB)
Me.SummaryTB = DLookup("[Summary]", "[Findings]", "[AuditItemID]=" & Me.AuditElementTB)
'Global Variables to push to Mandatory POI form
yestot = Me.ElementLB.Column(2)
notot = Me.ElementLB.Column(3)
natot = Me.ElementLB.Column(4)
doctotal = yestot + notot + natot

The only control the entry staff can write to on the opening form is Summary.

Then that information needs to push to the Findings TBL which I did as such...

Private Sub cmdSave_Click()
If IsNull(Me.MPOISummaryTB) Then
dummy = ReqMsg("MPOISummaryTB", "Summary")
GoTo CmdSave_ClickDone
End If
[AuditItemID] = Me.MPOIAuditItemsTB
[POIDocNum] = Me.TotalTB
[POIYes] = Me.YPercTB
[POINo] = Me.NoPercTB
[POINA] = Me.NAPercTB
[Summary] = Me.MPOISummaryTB
DoCmd.RunCommand acCmdSaveRecord

CmdSave_ClickDone:
Exit Sub

CmdSave_ClickError:
Warning Error$, "CmdSave_Click"
Resume CmdSave_ClickDone
End Sub


Three things:
1. The data will write to the table but if if the user has to post another finding it rewrites over the first entry. I did try to have it write to an intermediary table where I would then do an append or update query but it had the same issue. I would rather have it write to the Findings TBL directly.

2. The form won't close when finished. If I put in a DoCmd.Close it errors out.

3. Although the data will load for the next step (editing the POI entry with the data prefilling that has been entered), once you edit any info on the POI it resaves it to the Findings TBL as an entirely new entry - making duplicates for the basic information.

 

[wiklendt]

OK - I tried to post this Monday and everyone said I needed to have bound controls. That isn't going to work with a prefilled form.

I have a form that has the following controls predefined based on a selection of a Listbox:

actually, this sounds EXACTLY like the sort of situation that you NEED bound forms for.

i use bound forms almost exclusively, and i also almost exclusively have data 'prefill' based on a selection from a list box, on those bound forms. if you don't want users to change data, simply make those textboxes 'locked'. it also sounds as though you need a subform for your summary data, and to have that as a related, separate table to your main project(?) data.

how about you post your DB and let us have a look - a working example is worth a thousand words.

edit: also, if you base your forms on queries rather than tables (as you should), you have much more flexibility about the data you want to display or edit.

edit: added picture. in this picture you can see what i have in one of my databases. it is a bound form. the listbox on the left shows all the samples i have in storage. i can click on one (e.g., PSA, as shown) and it fills all the data in the form. there is also a subform where i can add extra stores for this sample. if i wanted, i could make the textboxes locked for editing (beware, i think if you make the form itself "no edits" that it passes that trait onto subforms).

 

[txgeekgirl]

Thank you so much for your reply - My DB is too large to post - even zipped.

I made an append query for the MandatoryPOI data that needed to go to the Findings TBL and slapped an IF statement around it to look for the AuditItemsID to make sure a POI wasn't already in place. I already had all the prefill stuff locked. It all worked great.

I am moving on to using an update query for the actual POI and having it search once again based on the ID so it doesn't duplicate.

I will have to read up on bound forms. I almost always write code to place control values.

Oh - a site that did help me yesterday was DataPigTechnologies.com.

 

[wiklendt]

if you want to jump in with both feet and see how easy bound forms from design view can be (as opposed to the wizard, which kinda does it for you) here's a quick how-to:

1) create new form in design view.
2) press the elipsis ("...") in the "record source" property field (data tab) of form - this brings up a query design
3) make a query to reflect the data you want in your form (this can just be all the fields from one table)
4) now click on the "field list" tool and plonk your desired fields into the form.

et viola! you have a bound form. and it's based on a query already, so if you ever wanted to filter it or anything similar, it's easy done by returning to the "record source"

 

[wiklendt]

txgeekgirl, did you manage to get this working for you?

 

[txgeekgirl]

I sure did, thank you. In fact, I released the DB a couple weeks ago and just gave them a few more features today. I have 2 more reports to go and it's done. A lot of it was fixed by using global variables to pass values and then having it write to the record as an append if new - show on output if existed. I used some SQL code as well for any pre-populating values.

Thank you so much for checking.