Need assistance with code to create a new record with pre-existing information

[Man_Over_Game]

Quick summary:
I have a combobox that shows a list of possible primary keys. If it's already existing, the form will then change to that record. If it doesn't exist, I want it to create a new one using the selection from the combobox. I don't have much experience with coding, and I'm hitting a wall on how to solve this.


Exhaustive summary:
I'm tracking workout routines for Persons, with their Months, and for each of their Days.

tblPersons and tblMonths have a junction table, tblPersonsMonths, that is using the child table tblDays.



In my main form, I pull the Person I want, and put that into a subform linked to tblPersonsMonths. I am using an unbound combobox that shows my full list of Months, and it needs to do 2 things:

Search for the record that contains the MonthID of my chosen selection from the combobox (which works perfectly)
ELSE
Create a new record that utilizes the selection from the combobox and the PersonsID from the Main form.

It's the ELSE part that I need some help with. Any awesome folks able to give some advice on this?

[Micron]

I gather that you make a selection from an unbound combo on the main form, wherein the main form is linked to the subform by some field that is common to both. If there are records, the Child / Master link property between the 2 forms is taking care of displaying the records.

You seem to be asking for a code solution but by not providing the code you've tried, you're not shedding much light on what's behind the form so far. Your post suggests that you're controlling the record(s) displayed via code now (because of the Else part) when that could be simply taken care of by the child/master link. Maybe all you need is to execute an Append query in code, then refresh the forms. First though, you'd need to ascertain if there are any records retrieved by the combo selection.

Maybe create a recordset, test for BOF and EOF being true (meaning there are no records) then execute the append query sql after the Else statement. For that I'd use the Execute method of the DoCmd object, then refresh.

[ssanfu]

@Man_Over_Game

So is your other thread solved (https://www.accessforums.net/showthread.php?t=72692) or did you give up on it?
I posted an example dB (in the other thread), but maybe you would post your dB so we can see what you are trying/have tried?

[orange]

I agree with Steve's comment. You have started this new thread, but it sounds like a follow-on to the previous question.
I also note that you had the same post on UA and did have responses.
It seems you are attacking you issue step by step as you encounter difficulty.
Posting a copy of your database would be helpful to readers to see what you have done.
I think it would also be helpful to you and readers if you could describe "your workout tracking business" in simple terms. If you give readers the big picture (30,000 ft level and then add a little detail), I'm sure you will get focused responses.

The key is to distinguish WHAT you need from HOW to do it in Access.

Good luck.

[Man_Over_Game]

@Orange and @ssanfu, You're right, and I would prefer to upload my DB if I could, but my business has some very restrictive firewalls that prevent me from doing a lot. I'm actually quite surprised Access even functions on our systems.

I will post as much detail as I can, though. I should have done that in the first place.
-------------------------------
My goal is, in a way, twofold:
1. Create a database for tracking workouts done each month, per each person, per each day, for the sake of researching workout trends, handing out yearly/monthly awards, and other such things.
2. Gain the knowledge needed to make a functioning junction table with a child table attached to it. Junction tables with a child table is an extremely practical utilization of Access, but it's also stupidly complicated for a novice like myself, and I'm probably the biggest expert on Access in my office. I solve this, I solve a lot of problems.

People have provided solutions that avoid using a junction+child table, such as:
using a Date field combined with queries that just combine or remove the information I don't want, rather than having tables for Months and Days;
using a third compound key on the Junction table (for the day) and adding the workout information on the same table and deleting the Days table.

And those solutions fix problem #1 (even better than my original idea), but I'm still trying to find a solution for #2.

I've found some code online that simply selects the record I want if it exists, and has an Else statement in case it doesn't.
I don't know what I should be looking for in that Else.

Private Sub cmbMonSel_AfterUpdate()
 If IsNull(Me!cmbMonSel) Then Exit Sub

    With Me.RecordsetClone
      .FindFirst "[PMonthID] = " & Me!cmbMonSel
      If Not .NoMatch Then
         If Me.Dirty Then Me.Dirty = False
         Me.Bookmark = .Bookmark
      Else
         ' putting the code needed to fill in a blank record here
      End If
    End With
End Sub

Note: PMonthID is the child version of the autonumber in tblMonths.


tblPersons and tblMonths are parents to tblPersonsMonths

tblPersonsMonths is a parent to tblDays.

My main form selects the tblPersons record I want.
My subform is using tblPersonsMonths, and has an unbound combobox (cmbMonSel) that is who's row source is tblMonths and has the code listed above.
My subsubform is connected to tblDays, which is function just fine as long as I have the proper junction selected.

So overall, my main form selects my Person, my subform is connected with my junction table, and I have a combobox, inside of my subform, used to search for the junction record that matches with my table of months. It doesn't do anything if the month I select doesn't have a junction link first, and that's what I'm hoping to solve.

[Man_Over_Game]

I just found out what my problem was. I assumed there was an issue with my database because of a misunderstanding with the junction table updating properly, but really, I was just an idiot.

The whole issue lies with the fact that my junction table has no information in it other than two foreign keys (which it uses as its own Primary Keys) and an autonumber for its child table to use. For Access to naturally generate an autonumber in a form, new information has to be provided into another field. I misunderstood this, tried a way to force the record to generate, but really, my issue was solved by just adding a field that I manually modify.

I'm sure there's a more graceful way of solving it, but for now, this solution will work until I find a way to make the combobox search/create thing to work properly. Thanks for the help and advice, guys.