Requery?

[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. My current code for the "Finish" button on the AddEditClients form is below. There is some extra stuff in there b/c I have been playing around with this for a day or two now.

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?) I had toyed around with closing one form before opening the other but then I was unable to pass back and forth my client id (key index number). As it stands now, I can not get my data refreshed (mainly a problem if a new client was added). If I put in a requery statement then I lose my position on the Client Form. So now the user would have to search for the client again. I feel like I am running in circles.

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.

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "Clients"
    If Me![ClientK_ID] > 0 Then
        DoCmd.Close acForm, "AddEditClients", acSaveYes
                                
    Else
        DoCmd.Close
                        
    End If

Basically I just want to be on a client... click an "edit" button to change their info. Then click a "finish" button and return to a mostly read only screen for that client. Or if I need to add a new client... click an "add" button... add a client then click "finish" and open that newly added record as mostly read only.

Unfortunatly I can't do this as a mode change... because on the Client form there are a few fields that are not read only.

Please help me. Thanks.

 

[ejstefl]

Well, what you'd want to do is first save the pk of the record you want to go back to when you click "Finished". Then you'd requery the Client form, and search for that pk again, then move to that record.

What I would do is open the addedit form as a popup, so code will be suspended until you close the form. Then after the form is closed, you can run code similar to this:

Set rs = Me.RecordsetClone
me.ClientID = strClientID
me.requery
rs.FindFirst "[ClientID] = srtClientID
If rs.NoMatch Then
       MsgBox "Error"
Else
       'Display the found record in the form.
        Me.Bookmark = rs.Bookmark
End If
        Set rs = Nothing

Alternativley, you could just have your edit buton unlock the locked fields...

 

[jeffm3434]

Set rs = Me.RecordsetClone
me.ClientID = strClientID
me.requery
rs.FindFirst "[ClientID] = srtClientID
If rs.NoMatch Then
       MsgBox "Error"
Else
       'Display the found record in the form.
        Me.Bookmark = rs.Bookmark
End If
        Set rs = Nothing

Ok so I think you are describing what I am wanting to do... where does this code need to be placed (which form and part of which button)? And can you give a quick english description of what this code says? (I'm trying to understand it... but I just don't know enough about VB objects and commands to know what this is suppose to do.)

 

[ejstefl]

Well, you can put the code in several different places. Why don't you put it on the click event of the "finish" button right before you close the form. You would need to modify it slighty, as follows:

'Define your variables
dim rs as recordset, strClientID as string

'Set the variable rs to be a copy of the recordset of your form
Set rs = forms!Clients.RecordsetClone

'Set the variable strClientID to be the unique identifier for the record you just added or edited
me.txtClientID = strClientID

'Requery the Clients form, to get the info you just modified updated
forms!clients.requery

'Search through the recordset clone for your record
rs.FindFirst "[ClientID] = " &  srtClientID

'If you don't find it, do something...
If rs.NoMatch Then
       MsgBox "Error"

'If you do find it
Else
       'Display the found record in the form.
        forms!clients.Bookmark = rs.Bookmark
End If
        'Clean up
        Set rs = Nothing

This assumes you have a form named clients, a field named ClientID that is the primary key, and a controld named txtClientID that is bound to ClientID.

You might want to disable the close button on your add/edit form to force the user to click the finished button.

 

[jeffm3434]

for some reason rs.NoMatch is not valid. I do not have "NoMatch" as possible choice after typing in "rs." And if I just type it in anyway I get a compile error.

also same with "FindFirst" but there is a "Find" and I switched to that... does that matter?

Is this a VB version issue? I'm using VB 6.3 with Access 2002. The screwy thing is that I see references to these things in the VB help file but it will not let me use them.

I tried to just comment out all of the If statement and only leave the line "forms!clients.Bookmark = rs.Bookmark" and run to see what happens and I get a "Type mismatch" error with no explanation. Here's what I have right now.

ejstefl said "and a control named txtClientID that is bound to ClientID." ... does this just mean the label that is attached to the field of ClientK_ID (the actual data field name)? I changed my label box to be named "txtCleintID" to make sure it matches your code. Is this what you meant?

    'Define your variables
    Dim rs As Recordset, strClientK_ID As String

    'Set the variable rs to be a copy of the recordset of your form
    Set rs = Forms!Clients.RecordsetClone

    'Set the variable strClientID to be the unique identifier for the record you just added or edited
    Me.txtClientID = strClientK_ID

    'Requery the Clients form, to get the info you just modified updated
    Forms!Clients.Requery

    'Search through the recordset clone for your record
    rs.Find "[ClientK_ID] = " & srtClientK_ID
 
    'If you don't find it, do something...
    'If rs. Then
     '  MsgBox "Error"

    'If you do find it
    'Else
       'Display the found record in the form.
        Forms!Clients.Bookmark = rs.Bookmark
'End If
        'Clean up
        Set rs = Nothing
    
    DoCmd.Close acForm, "AddEditClients", acSaveYes

 

[ejstefl]

No... if you right click on the control, click on properties, and then click on the other tab, you will find the name of the control. Access does sort of a stupid thing and names the control the same name as the field to which it is bound. This is generally bad practice, as it confuses things. The safest thing to do is rename your controls with a prefix corresponding to the control type (ie, prefix text boxes with txt, combo boxes with cbo, list boxes with lst).

At any rate, you just need to make sure that the control on your form is the same as you reference in the code. Make sense?

Also, you could try rs.findfirst... I'm not sure why rs.nomatch doesn't work for you...

 

[jeffm3434]

Ok so I changed the control part... but I still get a type mismatch error. Any ideas what that could be coming from?

 

[ejstefl]

What data type is [ClientK_ID] ?

 

[jeffm3434]

What data type is [ClientK_ID] ?

autonumber field

 

[ejstefl]

I don't know. If you want, post the db and i'll take a look.

 

[RV]

Change this line of code:

Dim rs As Recordset, strClientK_ID As String

in

Dim rs As Recordset, strClientK_ID As [B]Integer[/B]

RV

 

[RuralGuy]

PMFJI RV but I think the OP may need a Long for that AutoNumber field just to be safe.

Dim rs As Recordset, strClientK_ID As [b]Long[/b]

 

[jeffm3434]

Thanks for the suggestion guys... unfortunately I could not get any of this to work like I needed. So I just modified my design slightly (not as elegant as I had in mind but it works).

Basically I leave the main form open if they click the edit client button. Then when they close the edit client form the underlying main form is already open and has any changed data in it.

If they are on the main form and click to add a new client... then I close the main form and then open the the add form (same form as the edit form) and then when they close the add form I just reopen the main form (which basically covers my original requery issue). They then have to do a quick search to find the new client they just added. This is not as bad as it seems in that they do not add new clients very often (maybe one or two a week at most).

 

[MartijnR]

I think you have a problem using vba objects

I've experienced funny things using them. For example in 1 database I have several forms. On 1 form I use vba the way it's supposed to, object related and it works.

When i copy/paste the same lines into another form, I get the compile errors described above and also other/no dropdownbox with keyswords I can use.

So I'm not sure if it's a reference problem, but if anyone can explain what it is??? THANKS!!!!