Update main form and go to specified record

[PeregrinTook]

Evening all,

Having a bit of a problem with a 'cattery management' database I'm working on, wonder if anyone can suggest anything pls...

From the main form "OwnersAndCats" (Main table from 'tblOwners', subform from 'tblCats') the user can either add a new cat for a current owner or a new owner, both of which are popup forms.

If the user adds a new cat to the currently selected owner, when they save and close the data entry popup form 'AddCat' I want the main form "OwnersAndCats" to be updated with the newly added cat and the form to display the owner which has just had the cat added.

If the user adds a new owner, they can either just add an owner and return to the main form or go on to add cats before returning - but either way I want the same thing to happen, ie the end result is having the main form "OwnersAndCats" displayed at the record which has just been added.

The problem I'm having is like this. The code below seems to work perfectly if I add a new cat to one of the six sample owners I imported to the table 'tblOwners' from an excel spreadsheet, but when I try it on newly created owners I get the message 'You cant go to the specified record'. Even after I get this message when trying it on one of the newly created owners I can go back to adding a cat for one of the original six and it works perfectly.

I also get the same message when I try to create a new owner then close out back to the main form. I'm sure it must be a very simple thing I'm doing wrong so if anyone can spot the mistake I'd really appreciate it! In fact if anyone can even suggest an easier way to achieve what I'm trying to do that'd also be very useful - the code's untidy I know, but I am very much a novice still - the only reason I've used the method below to show an updated form is because I'm led to believe you can't just close a popup form and simply refresh the main one behind it...

Here's the code for btnACSave on form "AddCat"

Private Sub btnACSave_Click()

    Dim stDocName As String
    Dim stDocName2 As String
    stDocName = "OwnersAndCats"
    stDocName2 = "AddCat"

On Error GoTo Err_btnACSave_Click

    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
    
    YesNo = MsgBox("This cat has been added successfully, do you want to add another cat for this owner?", vbYesNo + vbQuestion, "Add More Cats?")
      Select Case YesNo
      Case vbYes
        DoCmd.GoToRecord , , acNext
      Case vbNo
        Select Case stFormName
        Case "OwnersAndCats"
            DoCmd.Close acForm, stDocName2
            DoCmd.Close acForm, stDocName
            DoCmd.OpenForm stDocName
            DoCmd.GoToRecord acDataForm, stDocName, acGoTo, stLinkOwnerID
        Case Else
            Call Init_Globals(Me, OwnerID)
            DoCmd.Close
        End Select
      End Select
    
Exit_btnACSave_Click:
    Exit Sub

Err_btnACSave_Click:
    MsgBox Err.Description
    Resume Exit_btnACSave_Click
    
End Sub

And here's the code for btnAOSave on form "AddOwner"

Private Sub btnAOSave_Click()
    
    Dim stDocName As String
    Dim stDocName2 As String
    Dim stDocName3 As String
    Dim stLinkCriteria As String

On Error GoTo Err_btnAOSave_Click

    stDocName = "OwnersAndCats"
    stDocName2 = "AddOwner"
    stDocName3 = "AddCat"
    
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

    YesNo = MsgBox("The owner has been added successfully, do you want to add a cat(s) now for this owner?", vbYesNo + vbQuestion, "Add Cats?")
    Select Case YesNo
        Case vbYes
          Call Init_Globals(Me, OwnerID)
          stLinkCriteria = "[OwnerID]=" & stLinkOwnerID
          DoCmd.OpenForm stDocName3, , , stLinkCriteria
        Case vbNo
          YesNo = MsgBox("Do you want to add another owner?", vbYesNo + vbQuestion, "Add More Owners?")
            Select Case YesNo
            Case vbYes
              DoCmd.GoToRecord , , acNext
            Case vbNo
              Select Case stFormName
              Case "OwnersAndCats", "AddCat"
                  DoCmd.Close acForm, stDocName2
                  DoCmd.Close acForm, stDocName
                  DoCmd.OpenForm stDocName
                  DoCmd.GoToRecord acDataForm, stDocName, acGoTo, stLinkOwnerID
              Case Else
                  DoCmd.Close
              End Select
        End Select
    End Select

Exit_btnAOSave_Click:
    Exit Sub

Err_btnAOSave_Click:
    MsgBox Err.Description
    Resume Exit_btnAOSave_Click
    
End Sub

And finally my global variables:

Option Compare Database
Global stLinkOwnerID As Integer
Global stFormName As String

Option Explicit


Public Sub Init_Globals(rfrm As Form, OwnerID As Integer)
    stLinkOwnerID = rfrm.OwnerID
    stFormName = rfrm.Name
End Sub

Many thanks for taking the time to help!

 

[PeregrinTook]

Looking back at my post I think I may have asked too many questions, let me try to narrow my request down a little to just the main issue i'm having... I have also attached a copy of the database so you can see the problem for yourself - I really hope this helps someone to help me with the problem...

So just to clarify: The symptoms of the main problem are as follows:

When I add a new owner via a popup form then save, close & try to go straight to that new owner in the main form I get error message 'You cant go to the specified record'.

When I add a new cat (for either a new or existing owner which is not one of the first 6 I imported as samples) via a popup form then save, close & try to go straight to that new cat in the main form I get error message 'You cant go to the specified record'.

But when I add a new cat (for one of the first 6 owners I imported as samples) via a popup form then save, close & go straight to that new cat in the main form it works fine.

Hope the sample DB helps someone to understand & ultimately help me fix this problem...

Many thanks for taking the time to read this, any advice gratefully received.

 

[PeregrinTook]

Resolved!

Just for the record in case this helps anyone in future, I solved the problem - simple mistake from a newbie I guess...

After a great deal of searching & frustrating trial & error I narrowed it down to my DoCmd.GoToRecord statement, and discovered that the 'offset' parameter (last one - stLinkOwnerID in my original case, now set to newly defined golbal variable stRecordNo) refers of course to the actual record within the form rather than the OwnerID for that record - so even although the OwnerID might be 36 it shows as record 7 in the form, no wonder it couldn't find the specified record!

All the best
PeregrinTook

 

[Fear Naught]

The sub procedure Init_Globals() fails. I get an Oject Library error when trying to pass the parameters to the sub procedure from the add owner form.

 

[PeregrinTook]

Fear Naught,

I'm not sure if you're meaning to help me, or you want me to help you now I resolved my problem?

If you're trying to help me then many thanks but it's ok I got my problem sorted - however if you want me to help you in doing something similar let me know and I will post a copy of the database later tonight when I get home...

Cheers
PT