How do I go to the end of a record set and make a new record?

[Jacob-Bushnell]

Hi, I have this code I am using in one form to open a "sub" form in MS access 2002.
It calls the ID from the sub-form and goes to the record that has the matching ID.
My problem is when the sub form does not have that paticular ID it just opens the first record.

How do I make it so IF there is no ID matching THEN go to the end of the record set, make a new record AND put the ID from the originating form in to the ID of the new record I just created.

Clear as mud?
Any way, here is my code I am using to open it right now.

AdoptFam is the name of the Check Box

Private Sub Command116_Click()
If AdoptFam = True Then
DoCmd.OpenForm "FrmAdoptF", acNormal, , , , , Me!LngFamilyID.Value
Else
End If
End Sub

'And the code for the ID on the sub form...


Private Sub Form_Open(Cancel As Integer)

Dim strFamID As String
If Not IsNull(Forms!FrmAdoptF.OpenArgs) Then
strFamID = Forms!FrmAdoptF.OpenArgs
If Len(strFamID) > 0 Then
DoCmd.GoToControl "FamilyID"
DoCmd.FindRecord strFamID, , True, , True, , True
End If
End If
End Sub

Thanks a lot, Jacob

 

[Pat Hartman]

Remove any code you presently have in the pop-up form.
Modify your OpenForm to include the where argument.
In the Pop-up form, in the BeforeInsert event you need a single line of code:

Me.YourForeignKey = Forms!YourOtherForm!YourPrimaryKey

 

[Jacob-Bushnell]

I understand what you are saying but.. I am not very experinced at this yet. What is the syntax for the WHERE aruguement?
Thanks again, Jake

 

[Pat Hartman]

"TheTableFieldName = " & Me.YourFormControlName

 

[Jacob-Bushnell]

I am sorry but I still cant get it to work.

This is what I have changed it to on the main form:

Private Sub Command116_Click()
If AdoptFam = True Then
DoCmd.OpenForm "FrmFamAdopt = " & Me.LngFamilyID.Value
Else
End If
End Sub

And this is the Popup form code:

Private Sub BeforeInsert()
Me.FamilyID = Forms!FrmFamilies!LngFamID
End Sub

I am certianly not doing somthing right!


If Some one wanted I woulden't mind emailing a copy of the DB so they could look at it.
Thanks a lot, Jake

 

[bradcccs]

The structure of your OpenForm command is incorrect.

Try:

docmd.OpenForm "FrmAdoptF",,,"FamilyID = " & Me.LngFamilyID

Note: FamilyID should refer to the ID on the form to be opened, amd Me.LngFamilyID should refer to the ID on the current form (the one with the open button on it).

HTH

Brad.

 

[Jacob-Bushnell]

Thanks Brad! It works! (well almost) it is now giving me a error message when I open the pop-pup form that says:

" The expression MouseMove you entered as the event property setting produced the following error: Member already exsits in an object module from which this object module derives. "

I have atached a copy of the code if you wanted to take a look at it. Thanks for all your paticentce!
Jacob Bushnell

 

[bradcccs]

The code you posted does not give me any clues.

The only code functioning on posted list is the "BeforeInsert" code as specified by Pat.

This event does not "fire" until the user starts to type in the blank record (but works before a new record is created - thus lets you assign the PK)

If you are getting the error before typing, the BeforeInsert is not the problem.

Can you paste the code from your primary form, I suspect a further event is firing, and causing the problem.

Brad.

 

[Jacob-Bushnell]

Thanks Brad, Here you are.

 

[bradcccs]

Still no clues - (I am getting dumber and dumber by the minute trying to read your code )

There seems to be no events that would fire due to lost focus etc.


According to our good friends at Microsoft, this error occurs when a Sub procedure and a form object have the same name.

Check that your form object names do not overlap with the sub names.

You might want to delete all the irrelevant / inactive subs too.

If you have:

Private Sub SomeSubName()

End Sub

then the sub is doing nothing, so remove it.

Let me know how you go.

Brad.

 

[Jacob-Bushnell]

Right on Brad! I had a fragment of a vestigal On load Sub messing every thing up.
Sorry about the messy code But Hey, everyones got to start somewhere!
Thanks a Million

Jake