Set the Default Value of Field in Subform

[Micron]

Your db has linked tables but you didn't include them. If you include that file I'll add it to the same folder and relink them and alter the code accordingly. Or to reiterate the prior instructions (and if intAvailabilityID is in fact a number field):
- open in design view the table that contains the field you want to default to zero in the form.
- set its default property to 0 (will affect new records only). Save and close table.
- open new query in design, add this table, change query from "select" type to" update query" and add that table field
- in the update to: row, enter 0


- in criteria for that field enter = Null

run it and open your table and check if the records that had no entry now contain zeros.

[kidodynomite66]

This is the query that the subform uses . . . is it possible to somehow set the Criteria?

[kidodynomite66]

Will give it a go . . .

[kidodynomite66]

Morning Micron-

I attempted to follow your instructions. Below are images of my results . . . apparently, I did something wrong.
I also attached the db with its linked tables.

New Compressed (zipped) Folder.zip

[kidodynomite66]

Again, my goal is for every new record in the subform to have the same intAvailabilityID that's in the mainform.

[Micron]

I'm guessing that you changed the subform controlsource to this new update query? That wasn't suggested.
Seems to work if I put it back to what I think it was - Query.qryTempTPPtest? Had to alter the linked child field as well & chose Query.qryTempTPPtest; not sure if that is correct.
I'm assuming you changed that too.

You'll notice that your main form is in edit mode when it displays - because of your code that is setting field values. If you want/need to commit those values, add this to your load event:
If Me.Dirty Then Me.Dirty = False

Last but not least, I'm not a fan of using tables or queries as subform controlsources. Basically, it's the same as users tinkering directly in tables, which is something they shouldn't be allowed to do. Better to base subform on a form which in turn is based on a query IMO, as field validation is difficult or limited if not impossible.
Hopefully you're good to go now.

[Micron]

OK, saw your last post after I posted. I guess we're not there yet. My intention was to fix the nulls in your data first but it seems I forgot the original request. Hang on.

[kidodynomite66]

This was the original select query of the subform:

[Micron]

In post 9 your subform has several records but the db you posted seems to have only 1 in the table. OK, I added records via the query and I see them when I open the table or the query, yet your subform only displays 1 record. So more records in your posted db might have been a big help. Also, If you changed the linked master and child property because of using the update query instead perhaps I've chosen the wrong field (IDTPPFE). No doubt you noticed that Access balks when you click in either of these property rows because of the subform relationship is unbound. I also just noticed that now the main and subform source is the same query and that makes no sense.

I save zipped files in a folder for an OP. When you name subsequent uploads the same, I usually delete the old one rather than have a bunch of folders. Now I have to go back to the old zip and figure out if I have the wrong subform source. If not, then I'm totally lost as to what's going on and why a main and sub would be based on the same query.

[Micron]

This was the original select query of the subform:

So you uploaded a sample db with no records in the table behind that query? That's kind of frustrating and confusing. You modified the query rather than create a new update query to update the nulls to zero? I'm totally lost.

[kidodynomite66]

I understand. I will insert data and resend everything . . .