Setting pre-defined number of records and their values in subform

[MikeN]

I'm trying to create a subform that has a pre-defined number of records when it opens so the user cannot miss entering one. On my form, I'm needing 50 records where each record is incremented by 0.5 (e.g., 0.5 through 25.0). In short, this is what I'm looking for (image below)...with the exception of all records (0.5 - 25.0 in 0.5 increments) already being defined versus 'manually' entered by the user. Eventually I'll format the special effects of "Point" so it appears as if it was just a 'text' and not a fillable box. I presume this requires some level of advanced coding...which I am not super strong at yet...so if so, any example code would be extremely helpful.

Attachment 22900

[JamesDeckert]

Sounds like you'd need to create an append query to create the records when the form opens (filling in the Point field), or add the records using a recordset when opening the form. Then don't allow adding new records on the form.

[MikeN]

James,

Thanks for the suggestions. I'm not familiar with recordset but I've been doing some searching online and slowing picking up some concepts - this is mostly why there is a lag in my reply. Do you have any pointers on the code structure for an add records (0.0 to 25.0 at 0.5 increments) using recordset? This is where I'm at but it keeps crashing ("Not Responding") Access when I try to load the form. Also, the form I provided the image for is a subform, if that has any implications. My code is being done in the subform design on 'form load.' Are there any specific 'form settings' that I need to define?

Private Sub Form_Load()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim i As Single
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset("tbl_LineSurveyData", dbOpenDynaset)
           
    i = 0
    Do Until i = 25
    rs.AddNew
    rs!TransectPoint = i + 0.5
    Loop
        
End Sub

[MikeN]

Bump...

Is anyone able to help walk me through this? Or suggest alternative options? I've tried doing append queries and recordset (with the limited knowledge I gathered online for both of those) with no luck...I'm to the point now that I don't even really remember what all I've tried to piece together from forums and online tutorials.

[June7]

Don't really need to open a recordset to write records.

Dim x As Double
x = 0.5
While x <= 25
CurrentDb.Execute "INSERT INTO tbl_LineSurveyData(TransectPoint) VALUES(" & x & ")"
x = x + 0.5
Wend

What are these records related to? Don't you need some foreign key identifier that associates these records with some parent record?

An alternative is to use code that sets the TransectPoint textbox DefaultValue property with incremented value after user selects value from Option combobox. When user initiates next record the TransectPoint will populate with the DefaultValue.

[ssanfu]

Eventually I'll format the special effects of "Point" so it appears as if it was just a 'text' and not a fillable box. I presume this requires some level of advanced coding

Nope. Piece of cake... or is it Pie??

Open the form in design view
Click on the "Point" text box control
Open the properties dialog box (press the F4 key)
On the DATA tab,
....Set Enabled to NO
....Set Locked to YES

Save the form. Shazaaaam! Can't edit the POINT text box.



BTW, in your code in Post #3, you were missing one critical line of code:

    rs.AddNew
    rs!TransectPoint = i + 0.5
    rs.UPDATE
    Loop

And, as June pointed out, you are missing the FK field in your code.

[MikeN]

Don't really need to open a recordset to write records.

Dim x As Double
x = 0.5
While x <= 25
CurrentDb.Execute "INSERT INTO tbl_LineSurveyData(TransectPoint) VALUES(" & x & ")"
x = x + 0.5
Wend

What are these records related to? Don't you need some foreign key identifier that associates these records with some parent record?

An alternative is to use code that sets the TransectPoint textbox DefaultValue property with incremented value after user selects value from Option combobox. When user initiates next record the TransectPoint will populate with the DefaultValue.

Thanks June,
Yes, I feel like I've only been taking this concept one step at a time when I need to be looking into the whole picture at the same time since I'm asking for, what seems to me to be, a lot of things to happen. My FK is an autonumber (LineSurveyID). The more I play with it, using an autonumber seems like it would require extra steps since that number isn't assigned until the user begins data entry in the parent (like having to add a button that would manually run the While Loop and requery the subform once a LineSurveyID was assigned in the parent); versus having it all already visible on form load and/or open.

I'd rather not make 'extra steps' and I'd prefer to have it more visually appealing by not having unpopulated subforms when the parent form is opened - which is one reason I was initially trying to make this subform automatically show all the records that data can be entered into as soon as the parent is open. I actually considering getting rid of the autonumber and just incrementing by 1 based on the last LineSurveyID in the parent table. Is this bad form design to do that? If I did it that way, a FK could be assigned on the form open, the While Loop could run, and the subform would be populated with Point 0.5-25.0 when the form was opened, correct? Any suggestions, or better ideas, to simplify the process? I attached an image of the relationships - tblLineSurvey is my parent form and tblLineSurveyData is the subform.
Again, sorry for the lack of experience to make this happen without help/pointers. I kept putting this form off until last because I was hoping I could pick up more experience that would help as I went...which I have, just not all that I probably need yet!

[June7]

Generating custom unique identifier is a common topic. There are conflicting views on whether it is 'bad' design for this value to also be designated as PK/FK. I have a couple of dbs that do this. Then in other dbs I use autonumber.

You need to commit the parent record to table before the PK will be available. Record is committed when move to another record, close form, or run save code. User must input data for parent record, save, run code to create dependent records, open 'subform' with new records. So instead of the loop code behind the subform, put it behind the parent form, maybe a 'Save' or 'Open Point Form' button.

Dim x As Double
If Me.NewRecord Then
DoCmd.RunCommand acCmdSaveRecord
x = 0.5
While x <= 25
CurrentDb.Execute "INSERT INTO tbl_LineSurveyData(LineSurveyID, TransectPoint) VALUES(" & Me.ID & ", " & x & ")"
x = x + 0.5
Wend
End If
DoCmd.OpenForm "form name", , , "LineSurveyID=" & Me.ID

[MikeN]

Thanks again June!
I was able to do this on a button click:

Private Sub Command33_Click()
If Me.NewRecord Then
    DoCmd.RunCommand acCmdSaveRecord
    Dim x As Double
        x = 0.5
        While x <= 25
        CurrentDb.Execute "INSERT INTO [tbl_LineSurveyData]([LineSurveyID], [TransectPoint]) " & _
         "VALUES (" & [Forms]![frm_LineSurvey]![LineSurveyID] & "," & x & ")", dbFailOnError
        x = x + 0.5
        Wend
    'frm_LineSurveyData SubForm.Requery
    Me.frm_LineSurveyData_subform.Requery
 End If
End Sub

But might change it to open another form like you suggested because it isn't very appealing having the subform partially there.

Thanks again for all the help! Does Me.ID (in your code) directly refer to LineSurveyID in the parent form somehow? Or is it acceptable to leave it [Forms]!...![LineSurveyID]?

[ssanfu]

You might want to read these:

Microsoft Access Tables: Primary Key Tips and Techniques
http://www.fmsinc.com/free/newtips/primarykey.asp

(pay attention to "Don't use Primary Keys to Prevent Duplicate Records" )



Autonumbers--What they are NOT and What They Are
http://www.utteraccess.com/wiki/index.php/Autonumbers


All of my tables have an autonumber PK field.

[MikeN]

You might want to read these:

Microsoft Access Tables: Primary Key Tips and Techniques
http://www.fmsinc.com/free/newtips/primarykey.asp

(pay attention to "Don't use Primary Keys to Prevent Duplicate Records" )



Autonumbers--What they are NOT and What They Are
http://www.utteraccess.com/wiki/index.php/Autonumbers


All of my tables have an autonumber PK field.

I'm not 100% sure why I switched to using a composite key for tbl_LineSurveyData....this happened at some point during the 4-5 days of experimenting with recordset, update appendices, etc. Maybe it was just a tangent I got stuck on because it seemed like one less field in that table. Originally I had 'observationID' as an autonumber for each 'point' in the subform. However, within in each LineSurvey, there can be 4 individual transects; each transect has 50 points. So within one sampling day (four total transects) a total of 200 'points' may be generated. I probably fell into the trap of using the composite as a PK to reduce the 200 extraneous values but it may have been with the hidden thought of preventing duplicates too. Oops. When I query the data for analysis I will have to query it as observations at each point, within a transect, within a trip of transects (if that makes sense). Do you suggest that I switch back to an autonumber (observationID) as a PK versus a composite key in this case?

[June7]

Use your actual ID field or control name. Since what you have is working then don't change.

If the composite PK is not used anywhere as a FK, it doesn't matter. Then don't even need PK designation. Can simply set the two fields as compound index to prevent duplication pairs.

[MikeN]

Thanks for all the help everyone!