Open Form with no existing record

[miacino]

I am trying to create a command button to open another form, "CoMorbidities" with mutual MRN#. If the MRN# does not already exist in the "Comorbidities" form, I need to add the MRN# to that form/table based on the same MRN# that is on the open form where the command button is. (Me.MRN#).

I've tried several variations of code, and am not getting it to work.

 

[Minty]

Can you post up what code you have tried that didn't work, and what error you got?

This might get you some of the way there http://www.baldyweb.com/wherecondition.htm

 

[Gasman]

I've done this by filtering to the respective value and also passing it in as an OpenArgs

Private Sub cmdProgress_Click()
DoCmd.OpenForm "frmProgress", , , "CadetIDFK=" & Me.CadetID, , acDialog, Me.CadetID
End Sub

Then in the form

Private Sub Form_Current()
If Me.NewRecord Then
    Me.CadetIDFK = Me.OpenArgs
End If
End Sub

HTH

 

[miacino]

In the form I am trying to open ("Power CoMorbdities") (and add record ("CCMC#") if not already there) - I have this code:

Private Sub Form_Current()
If Me.NewRecord Then
Me.CCMC# = Me.OpenArgs
End If
End Sub


On the command button on the main form, I have:

Private Sub Command1082_Click()
DoCmd.OpenForm "power comorbidities", , , "CCMC#=" & Me.CCMC#, , acDialog, Me.CCMC#
End Sub



I get an error message as such: "Compile error - Method or data member not found"

 

[Minty]

This might be a field naming issue...

The # character is a date delimiter and sometimes a numeric wildcard, and special character and its use in a field name and or form control name might be the problem.

Try calling it something else without the special characters involved.

 

[miacino]

I know (now) that I should have not named these fields with a #. However, at this point, I am unable to change it as it is so embedded everywhere in the database.

 

[Minty]

Well - this might force you... Create or modify the query for the forms and simply give that field an alias for this one example, and see if it cures your problem.

If it does, then bite the bullet now and change the name(s). I've had to do it, on a big database (70K FE no data, with literally hundreds of objects/forms/queries etc)

You can buy some pretty cheap tools that will do most of the leg work for you, and help you day to day. MzTools is a good one.

 

[Gasman]

Minty,

That was just my way. ?
Is there another way where the user would not have to bite the bullet at the moment.?

I appreciate it would be worth doing, but at present the o/p has only been shown one way?

 

[Minty]

Simply rename those controls on this offending form, and adjust this one bit of code to match.

If it solves it, then I would seriously consider the pain, as it's bound to crop up again.

As an aside - i think it's a really bad idea to leave any controls with the same name as the underlying field, you end up sometimes referring to one when you expected to refer to another.

So simply rename the control to txtCCMCNo , and then use that as the Me.txtCCMCNo form reference.