Open form using openargs in new or edit modes

[mafhobb]

So I have a form (Call Listing Subform) that is loaded hidden and is bound to a table as recordsource. A second unbound form (frmrepair) is used to enter data into the form subcalls.

    DoCmd.OpenForm "SubCalls"
    [Forms]![SubCalls].Visible = False
    [Forms]![SubCalls]![CallID] = Forms![contacts]![Call Listing Subform].Form![CallID].Value
    [Forms]![SubCalls]![SubCallDate] = Now()
    [Forms]![SubCalls]![WhoPickedUp] = sName
    [Forms]![SubCalls]![WhatWasSaid] = Me.txtNewDetails.Value
    [Forms]![SubCalls]![StatusAfterCall] = StatusAfterContact
    [Forms]![SubCalls]![ResolutionDetails] = ResolutionValue
    [Forms]![SubCalls]![Label] = ""
    DoCmd.Close acForm, "SubCalls"

Right now the code knows what record in Subcalls to add the data by reading the value of a field in another form.... This is the Call Listing Subform and the common field is CallID. I basically open the subcalls form, enter the data and close it to save it into the table.

This works fine. Now I need to open another form from frmrepair that is called frmestimate. frmestimate is bound to the table "tblrepdescription" which contains an autonumber, CallID and Description as fields.

On an "on-click" event in frmrepair I have the following code to open frmestimate

    Dim CallIDvar As String
    Dim stDocName As String
    Dim stLinkCriteria As String
    CallIDvar = Forms![contacts]![Call Listing Subform].Form![CallID].Value
    stDocName = "frmestimate"
    MsgBox CallIDvar
    stLinkCriteria = "[CallID]=" & CallIDvar
    DoCmd.OpenForm stDocName, , , stLinkCriteria

And in the "on open" event of frmestimate I have this code

If Me.OpenArgs <> "" Then
    MsgBox Me.OpenArgs
    With Me.[frmEstimate].Form
        .Filter = "[CallID]=" & Me.OpenArgs
        .FilterOn = True
    End With
    End If

On frmestimate I have a a field "Description" bound to the table tblredescription so when I enter info here, it is saved in tblrepdescription.

I have handle two situations:

1-If this is the first time that frmestimate is opened for this CallID, it will have no matching record in tblrepdescription, therefore the form needs to open in "New Record" mode and CallID needs to be entered in the appropiate field along with the text entered in the description field.

2-If there already is a record for this CallID in tbldescription, then frmestimate needs to open in Edit mode so that the description can be edited as I only want ONE description for each CallID.

Suggestions?

thanks

mafhobb

 

[mafhobb]

All right I am trying to use the openform method but I am not having much luck. This is the code to open the form

Private Sub cmdEstimate_Click()
    Dim CallIDvar As String
    Dim stDocName As String
    Dim stLinkCriteria As String
    Dim AddEdit As String
    
    CallIDvar = Forms![contacts]![Call Listing Subform].Form![CallID].Value

    MsgBox "CallIDVar = " & CallIDvar

    AddEdit = Nz(DLookup("Description", "tblRepDescription", "CallID = " & CallIDvar & ""), "")
    
    MsgBox "addedit = " & AddEdit
    
    stLinkCriteria = "[CallID]=" & CallIDVar
    
    MsgBox "Stlinkcriteria" & stLinkCriteria
    
    If AddEdit = "" Then
        stDocName = "frmestimate"
        DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormAdd
    Else
        stDocName = "frmestimate"
        DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormEdit
    End If

End Sub

This is the code in frmestimate

Private Sub Form_Open(Cancel As Integer)

    If Me.OpenArgs <> "" Then
        MsgBox "OpenArgs Present" & Me.OpenArgs
        With Me.[frmEstimate].Form
            .Filter = "[CallID]=" & Me.OpenArgs
            .FilterOn = True
        End With
            Me.txtCallID.Value = Me.OpenArgs
    End If
    
    MsgBox "OpenArgs not present"
    
End Sub

When I run this, the first msgbox gives me "CallIDVar=8" which is the correct CallID. The second one gives me "addedit=" which means that there is no record for that CallID number in the table "tblrepdescription" which is correct. Then the third msgbox gives me "stLinkCriteria[CallID]=8" which I am not sure it is correct or not. Finally when "frmestimate" opens, I get the message "AopenArgs not present".

The openargs are not getting passed to frmestimate....why?

mafhobb

 

[mafhobb]

uhmmm

CallID in my table is a number, not a string. Does that make a difference?

mafhobb

 

[mafhobb]

Anyone has any ideas?

 

[mafhobb]

Can't figure it out...Anyone at all?

 

[mafhobb]

OK, so this is what I have now in frmrepair

Private Sub cmdEstimate_Click()
    Dim CallIDvar As String
    Dim stDocName As String
    Dim stLinkCriteria As String
    Dim AddEdit As String
    
    CallIDvar = Forms![contacts]![Call Listing Subform].Form![CallID].Value
    AddEdit = Nz(DLookup("Description", "tblRepDescription", "CallID = " & CallIDvar & ""), "")
    stLinkCriteria = CallIDvar
      
    If AddEdit = "" Then
        MsgBox "Open as New"
        stDocName = "frmestimate"
        DoCmd.OpenForm stDocName, , , , acAdd, , stLinkCriteria
    Else
        MsgBox "Open as Edit"
        stDocName = "frmestimate"
        DoCmd.OpenForm stDocName, , , , acFormEdit, , stLinkCriteria
    End If

End Sub

And on frmestimate I have this:

Private Sub Form_Load()
    If Me.OpenArgs <> "" Then
    MsgBox "OpenArgs Present = " & Me.OpenArgs
    Me.txtCallID.Value = Me.OpenArgs
    Else
    MsgBox "OpenArgs not present"
    End If
 End Sub

Now, when I click on cmdestimate, the msgboxes tell me if the new form is to be opened in edit record mode or new record mode (this seems to work correctly)

When frmestimate opens up, the field description is filled up with the appropriate value (if we are edditing the record) or blank (if it is a new record)

However, two things are happening:
1) if it opens on edit, changing anything will return the "this would create a duplicate in the table" so I cannot save the data that I want to edit

2) If It opens as a new record, I can enter the data fine, but if I use tab through all of the fields, it will exit the new record and start a new one.

What is happening?

mafhobb

 

[mafhobb]

heeeeeeelp!