design problem or requery problem?

[jeffm3434]

I have a form ("Clients") that points to a "Clients" table. I also have a form ("AddEditClient") that points to same table.

Open Clients form and you see client info. Have at the bottom 2 buttons. One to create a new client and one to edit the current client. B/c we do not want user to edit clients from main client form (most fields are locked on this form). So if the user clicks on "edit client" button takes them to same record on AddEditClient form... now they can edit the client (fields are not locked). If they click the "add client" button it takes them to a blank new record on the AddEditClient form. To this point all is good.

On the AddEditClient form there is only one button ("Finished" button). If they were editing an existing client I want it to return them to the same record on the client form and making sure to display the new data. If they added a new client then I want it to display that new client on the client form. If selected the new client button but then did not add a new client and simply hit finished then it opens the client form on the first record of the client table. I have it half way working right.

My problem is that if they added a new client then it does not send them back to that client. I think I need a REQUERY statement... but it seems that no matter where I put it, it breaks my opening of the correct record and then starts sending me back to the first record of the client table. Here is my code for the "Finish" button on the AddEditClients form. Where does the requery statement go (and what is the proper syntax)? (or if this is a bad way to do this... how am I "suppose" to do it?) Also I read on another thread something about opening the second form in "Dialog" mode... this keeps it so that the second form would have to be closed to get back to the first form. I think this would be a good thing to do here to... how do I do that?

Private Sub FinishedAdd_EditButton_Click()
On Error GoTo Err_FinishedAdd_EditButton_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Clients"
If Me![ClientK_ID] > Null Then
stLinkCriteria = "[ClientK_ID]=" & Me![ClientK_ID]
DoCmd.Close
DoCmd.OpenForm stDocName, , , stLinkCriteria

Else
DoCmd.Close
DoCmd.OpenForm stDocName, , , stLinkCriteria

End If

Exit_FinishedAdd_EditButton_Click:
Exit Sub

Err_FinishedAdd_EditButton_Click:
MsgBox Err.Description
Resume Exit_FinishedAdd_EditButton_Click

End Sub​

Thanks for any help you can give!!!

 

[wazz]

that's a lot of different scenarios. here's one way of getting back if a new record was added:

Private Sub cmdFinish_Click()

    DoCmd.RunCommand acCmdSaveRecord
    Forms!frmSite.Requery
    DoCmd.GoToRecord , "frmSite", acLast
    DoCmd.Close acForm, Me.Name

End Sub

here are some ways of opening a form:

DoCmd.OpenForm "frmYourForm", , , , , acDialog
DoCmd.OpenForm "frmYourForm", , , , acFormAdd
DoCmd.OpenForm "frmYourForm", , , , acFormEdit
DoCmd.OpenForm "frmYourForm", , , , acFormReadOnly

hope that gives you some new ideas.

 

[jeffm3434]

Ok... another try at this...

I think by changing some of my ideas here I get around one problem but have created a new problem... however the newer problem may be easier to fix (at least for those of you who know something about VB, unlike me )

So here is my new design (I just need a little coding help here):

Client Form ---> points to Client Table but is essentially read only
AddEditClient Form ---> points to same Client Table but has full access
ClientK_ID ---> the key index for the Client Table... the field I am trying to use to navigate the tables from the two forms.

User starts on Client Form. Does a search to find a Client. Decide they need to edit the client so they click the "Edit This Client" button. Button closes the current Client Form and opens the AddEditClient Form and jumps to the record they were on (actually it filters the list to match only the record they were on). Then they can edit the client and then click a "Finished Add/Edit" button. Now I want the current form to close and then reopen the Client Form and go back to the record they just finished editing (but do not want the list filtered). This method solves my original Requery issue but now I don't know the proper command to use.

So... How do I open the Client Form and jump to the record that matches the ClientK_ID that I was just editing from the AddEditClient Form? I never want my Client Form to be opened on a filtered Client table. Maybe a statement something like the "DoCmd.GoToRecord" but I don't know how to tell it to goto the record where the ClientK_ID matches the ClientK_ID of the record I was just on.


Code:

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Clients"

If Me![ClientK_ID] > 0 Then
stLinkCriteria = "[ClientK_ID]=" & Me![ClientK_ID]
MsgBox "ClientK_ID = " & Me!ClientK_ID
DoCmd.Close
DoCmd.OpenForm stDocName, , , stLinkCriteria <=== I think this is the problem line of my code. It is causing a filter of the Client Table.

Else
MsgBox "Else Loop = " & Me!ClientK_ID
DoCmd.Close
DoCmd.OpenForm stDocName
DoCmd.GoToRecord , , acFirst

End If​

 

[wazz]

on your AddEditClient form's finished button:

    DoCmd.OpenForm "Client", , , , , , Me.ClientK_ID
    DoCmd.Close acForm, Me.Name

in your Client form:

Private Sub Form_Open(Cancel As Integer)
    
    If Me.OpenArgs > 0 Then
    
        Dim rst As Recordset
        Dim strSearchName As String
    
        Set rst = Me.RecordsetClone
        strSearchName = Str(Me.OpenArgs)
        
        rst.FindFirst "ClientK_ID = " & strSearchName
            If rst.NoMatch Then
                MsgBox "Record not found"
            Else
                Me.Bookmark = rst.Bookmark
            End If
        rst.Close
    
    End If

End Sub

if there are any other ideas out there i'd love to see them, too.

 

[jeffm3434]

Private Sub Form_Open(Cancel As Integer)
    
    If Me.OpenArgs > 0 Then
    
        Dim rst As Recordset
        Dim strSearchName As String
    
        Set rst = Me.RecordsetClone
        strSearchName = Str(Me.OpenArgs)
        
        rst.FindFirst "ClientK_ID = " & strSearchName
            If rst.NoMatch Then
                MsgBox "Record not found"
            Else
                Me.Bookmark = rst.Bookmark
            End If
        rst.Close
    
    End If

End Sub

[/QUOTE]

Having several problems with these lines of code... I did away with the If statement b/c evidently there is no "NoMatch" command even though it is in the help file index... there is no actual topic on it. I did find it in one of Microsofts examples too but it doesn't compile with that in there. No big deal... I just dropped it. The main problem right now is a type mismatch error on the line:

Set rst = Me.RecordsetClone

Again, since I really know very little to nothing about VB... I don't know how to correct this. Any ideas?