Set the Default Value of Field in Subform

[kidodynomite66]

How do I set the default value of a field in a subform to equal the value of a textbox in the main form?



Both the mainform and subform are to be used for data entry. They are linked by an autonumber field "IDMF" in the mainform, and number field "MFID" in the subform (i.e., LinkMasterFields = IDMF; LinkChildFields = MFID).

For each one mainform entry there can be multiple subform entries.

There is one field in the subform in which I want it to be equal/set to a value from a textbox in the mainform (the data in the mainform's textbox is a preset number).

I've tried different variaitions of the following to no avail:
Forms!MainformName!SubformName.Form!MainformTextbo xName.DefaultValue = "'" & Me!MainformTextBoxName.Value & "'"

I also tried the following to see if I could simply make the value in the subform "3":
Forms!MainformName!SubformName.Form!MainformTextbo xName.DefaultValue = 3

I'm searching the forums and the web, but have yet to find the solution.

Any help/direction is appreciated.

-Kidodynomite66 (Novice Access/VBA developer)

[Micron]

You made those attempts where/how? That could be important. No error messages?
I suspect the main form current event would be the place to put that line except you don't need .DefaultValue. Simply make the subform control value be whatever is in the main form control. You are trying to set a property of a control, not it's value. I'm not sure but I think this default is determined at form load time and in your case, the property isn't there at that time. You're trying to set it after the fact. Try

Forms!MainformName!SubformName.Form!MainformTextboxName = Me.MainformTextBoxName

as I don't think you need the quotes either.

[orange]

Subform loads before main. Not sure how to have it show a value from the mainform.
Perhaps, untested - (a guess)
- subform loads, main loads, on mainform load set the value of the subform control.

[Micron]

Not sure how to have it show a value from the mainform.

In a main form event as I suggested - Current?
However, that will put the current subform record into edit mode.

[kidodynomite66]

Micron-
Using the code you provided, the value/data from the textbox in the Mainform is, indeed, placed in the designated field in the first record of the subform. However, the subsequent records in the subform are not being populated with the Mainform's textbox value.

The code is placed in the Mainform's load event.

[Micron]

I think I misinterpreted your meaning of "default value"? I took it that you wanted a field for one record to be a certain value. However, it seems that you actually want the default value property of a field to be that value so that additional subform records in that field will have that same value. Is that correct? Then if the value is a number as you indicated perhaps

Forms!MainformName!SubformName.Form!MainformTextboxName.DefaultValue = "=" & Me.MainformTextBoxName

[kidodynomite66]

The subform's ControlSource is a query. Is it possible to set the query's field to default to the value from the Mainform's textbox?

[Micron]

I think you mean subform control sourceobject is a query (i.e. it is a query, not a form)? As previously noted (I think) a subform loads first, so one trick is to set such properties of a subform from main form OnLoad code. Not sure what to advise based on your term.

If the sourceobject is a form, you can set its recordsource on main form opening.
If the sourceobject is a query, that query could refer to the main form control as a parameter. However, you'd still need to set the sourceobject of the subform control to that query in the main form load, otherwise when the subform loads the query would run and the main form isn't open yet.
Lastly, you might be able to set the sourceobject to an actual sql statement created in main form load event. Can't recall that I've ever tried that.
EDIT - forgot to say that IMO you're not really after setting a field default value property - you're after setting the default value of a control. The former implies that the property is to be set at the table level, whereas you want to do this to a control on a form. Technically there is a difference.

[kidodynomite66]

Yes, you're correct, I meant SourceObject.

I've attached an image.

The subform's sourceobject is a query of a temporary table of the actual linked table. The mainform's table also has a temporary table for its linked table. Upon hitting SAVE, the data in both temporary tables is inserted into both linked tables. I want to use "intAvailablityID" to eventually tie the subform's records to the mainform's record.

For this example, I want the 3 (or whatever other number is in the textbox) from the mainform's intAvailablityID to populate every intAvailabilityID of the subform. The top image is what appears when the form is initially opened. Again, "using the code you provided, the value/data from the textbox in the Mainform is, indeed, placed in the designated field in the first record of the subform. However, the subsequent records in the subform are not being populated with the Mainform's textbox value."

I'm still learning the lingo/terms so please bear with me . . . . I hope the image helps explain what I'm trying to accomplish.

[Micron]

Unclear as to which posted code you're referring to, post 2 or 6? Or both of them do this for only one record? I suspect both but I don't have anything to test with at the moment. I can understand why it would be only the first record regardless (if both codes result in the same effect). This suggests to me that the subform records might have to be updated instead but please confirm if post 6 code is doing the same thing.

[kidodynomite66]

Image attached.

Post 2: sets intAvailabilityID in the subform to equal intAvailabilityID from the mainform in the subform's first record. Subsequent subform records are being set to the mainform's intAvailabilityID. No error messages.
Post 6: produces the below error message when the form is opened.

The code is placed in the mainform's load event.
Code is in the main form's load event.

[kidodynomite66]

Ignore the image with Run-time error 2113 and its accompanying code . . . I did not intend to attach it.

[Micron]

Should have suggested this earlier. Consider setting the table field default value to be 0 in design view. Then update intAvailabilityID to 0 "Where intAvailability Is Null". A fixed design decision is makes much more sense than using code to achieve the same thing.

Usually ID fields are not used like this; they're either foreign or primary keys, often based on autonumbers, which can never be 0.
If for some reason that fix can't be implemented, perhaps copy, compact and zip db and post it here for analysis (and perhaps easier to implement the current idea if it must be the one to use). If you need to protect privacy consider Randomizing Data for Posted db's

[kidodynomite66]

Will give it a go . . . in post 11, I meant to say:

Post 2: sets intAvailabilityID in the subform to equal intAvailabilityID from the mainform in the subform's first record. Subsequent subform records are not being set to the mainform's intAvailabilityID. No error messages.

[kidodynomite66]

Micron-

I do not understand your instructions in post 13, so I attached (I hope my attempt worked) a copy of the db for review.

Thank you for your assistance.