OpenForm (Access 2k)

Topic

New Reply

I’m at my witts end….please someone help me…..

My table hold precert information and case mgmt information. I have txtSSN and txtMaxMC.

I have 2 qry’s working off this table.

1 qry for Precert and the other for CM.

I have 2 forms…1 for Precert and 1 for CM.

After I complete Precert, I want to open frmCM to the SSN and MaxMC.

I don’t want a new record…I just want the record that holds the SSN and MaxMC. However, I continue to get a NEW record, no matter how many times or ways I’ve changed the code to open frmCM.

The data for both are in the same table. Should be so simple…but for me it’s not.

Could someone please show me the error of my ways?

Thanks in advance.

Reply | Quote

Replies

Has the DataEntry property of frmCM been set to Yes? If so, it’ll always opened to a new record.

If not, please post the code you use to open frmCM.

Reply | Quote

Ok…I’ve turned off the Date Entry property…and that at least stops the NEW RECORD issue….but now when — On the precert form txtSSN=12345 but the frmCM will open to another SSN.

Here is a snippet of my code:

Private Sub cboReviewType_AfterUpdate()
'Update the subform and take user to appropriate forms
    Dim Msg, Style, Response As String
    Dim strEpisode As String
    Dim strInput As String, strMC As String
    Dim RS As DAO.Recordset
    Dim ctlMaxMCNo As Control
    
    Dim strCM As String
    Dim strHH As String
    Dim strIN As String
    
    Dim stLinkCriteria As String

    strCM = "frmCaseMgmt"
    strHH = "frmHomeHealth"
    strIN = "frmInpatient"
    
    Set ctlMaxMCNo = Forms!frmPrecert!txtMaxMCNo
 
           Select Case Me.cboReviewType
                Case 1
                    Msg = "Inpatient xFer?"    ' Define message.
                    Style = vbYesNo ' Define buttons.
                    Response = MsgBox(Msg, Style)
                        If Response = vbYes Then    ' User chose Yes.
                            strInput = InputBox("maxMC #?")
                            Me.txtMaxMCNo = strInput
                            ctlMaxMCNo.Requery
                            Me.txtReveiwType = "Case Mgmt."
                            DoCmd.Save acForm, "frmPrecert"
                            stLinkCriteria = "[MaxMCNo]=" & "'" & Me![txtMaxMCNo] & "'"
                            DoCmd.OpenForm strCM, , , stLinkCriteria
                            'DoCmd.OpenForm "frmCaseMgmt", acNormal, "", "", acEdit, acNormal
                            Forms![frmCaseMgmt]!lblComments.Caption = "Initial Comments"
                            Forms![frmCaseMgmt]!memIniComment.Visible = True

                        Else    ' User chose No.
                            Me.txtMaxMCNo = "Pending"
                            ctlMaxMCNo.Requery
                            DoCmd.Save acForm, "frmPrecert"
                            stLinkCriteria = "[MaxMCNo]=" & "'" & Me![txtMaxMCNo] & "'"
                            DoCmd.OpenForm strCM, , , stLinkCriteria
                            'DoCmd.OpenForm "frmCaseMgmt", acNormal, "", "", acEdit, acNormal
                            Forms![frmCaseMgmt]!lblComments.Caption = "Initial Comments"
                            Forms![frmCaseMgmt]!memIniComment.Visible = True

Perhaps someone can make sense of this mess….I’m so confused I can’t count to 3 right now.

Reply | Quote

You don’t include the SSN in the WhereCondition. It should probably be something like

stLinkCriteria = "[MaxMCNo]='" & Me![txtMaxMCNo] & "' And [SSN]='" & Me![txtSSN] & "'"

Replace SSN with the actual field name.

Reply | Quote

If you mean you ONLY get a new record (i.e. no existing record), then, as HansV suggested, the Data Entry property in the form might be on. If you meant that you get the record you want AND a new record, turn off Allow Additions.

Reply | Quote