requery error after auto info input

[Vsnow42]

Hi all I’m looking for help again, I thought I had solved a problem but have not. I have three database tables. One with part numbers description make etc in it and another with part number, location, depot and quantity in it, the third will allow booking in and out. This is in Access 2002 and using VB.

I then have a form with the master table and two sub forms on it to allow adding new parts and booking.

On one of the subforms the user enters a new location for a part already in the master table after using the inbuilt find function via VB code on a button. On the Lost Focus of the Location field I have the code below that sets focus to the location field and then the Qty field. The fields have in their respective Got Focus areas code that puts the depot in from a label that has previously been set, and a default Qty of 1. The curser remains on the Qty field so the user can change to another value if they require.

The problem is after the a new location has been entered and the automatic inputs are put in, when they select the find button to move to the next part number ( to add another location or select existing, in order to go to the other subform) it crashes. The normal Find input box comes up and you type the next part number, but when you select find it crashes. The error either brings up an input box asking for a parameter with the note “DepotListSubform.Form.Part!” then “Method ‘Requery’ of object ‘subform’ failed”
and no matter what you do everything gets closed down. With an obligatory do I want to tell Microsoft box.

I hope I have explained it properly and any suggestions of what I can do would be very welcome.

Additionally is there a book that people would recommend that explains this type of VB code for Access2002 (as I think I’m going to be stuck with this for a while). Please note this will be my own money paying for it.

Thanks
Vsnow42

The code on the Lostfocus of location which is on the subform is :-

If Me.Parent.ActStatus.caption=”Adding Tool” Then
Me!OwnerDepot. Setfocus
Me!Qty.SetFocus
Else
Endif

Again thanks to both dhookom and TheAceMan1 for their help in my last post on the same sort of subject.

 

[MajP]

Can you post the SQL of your subfor? My guess is when your subform requeries your search list is null. I am assuming your query references a control value.

 

[Vsnow42]

How do I attach the code ? there is a little icon that looks like a paper scroll with greater and less than signs on it, should I just paste into that ? I had some problems just submitting the post yesterday, first I didn't have a subject ( so fair enough) but after I would press it an nothing happened ? not sure why after about the 10th go it went in. So sorry for my ignorence in this.

There also seems to be an attachment bit below, but that is a url ( code is actually on another machine) So can I copy to word and post ?

Thanks

 

[Vsnow42]

Not sure if this is what was requested, but here goes.( Please note i'm quite a newbee at Access & VB, I have used and programmed in Paradox in the past)

Option Compare Database

Sub Form_Current()

    Dim ParentDocName As String

    On Error Resume Next
    ParentDocName = Me.Parent.Name

    If Err <> 0 Then
        GoTo Form_Current_Exit
    Else
        On Error GoTo Form_Current_Err
        Me.Parent![BookedSubform].Requery
    End If

Form_Current_Exit:
    Exit Sub

Form_Current_Err:
    MsgBox Error$
    Resume Form_Current_Exit

End Sub


Private Sub Location_Change()

Me.Parent.ActStatus.Caption = "Adding tool to depot"

End Sub

Private Sub Location_Click()

If Me.Parent.DepotLoc.Caption = "depot" Then
Me.Parent.Prefix.SetFocus
MsgBox " You must select a depot first"

Else
End If



End Sub

Private Sub Location_LostFocus()

If Me.Parent.ActStatus.Caption = "Adding tool to depot" Then
    Me!OwnerDepot.SetFocus
    Me!Qty.SetFocus

Else
End If


End Sub


Private Sub OwnerDepot_Click()

If Me.Parent.ActStatus.Caption = "Adding tool to depot" Then

Else
   If Me.OwnerDepot.Text = "" Then
       MsgBox "Enter a Location first"
       Me!Location.SetFocus
   Else
       MsgBox "You can not Change the Depot Once set"
       Me!Location.SetFocus
   End If
End If

End Sub

Private Sub OwnerDepot_GotFocus()

If Me.Parent.ActStatus.Caption = "Adding tool to depot" Then
    If Me.Parent.DepotLoc.Caption = "Depot" Then
        MsgBox "you should have selected a depot first"
    Else
        Me.OwnerDepot.Text = Me.Parent.DepotLoc.Caption
    End If
 Else
 End If

End Sub

Private Sub Qty_GotFocus()

If Me.Qty.Value > 0.1 Then
Else
Me.Qty.Value = 1
End If

End Sub

Private Sub Qty_LostFocus()

Me.Parent.ActStatus.Caption = "Tool added to depot"

End Sub

 

[MajP]

http://www.techonthenet.com/access/queries/view_sql2007.php

When you make a query even if using the designer it is stored in a SQL string. See the link. Can you provide the SQL. My guess it is that and not your vba that is the problem.

 

[Vsnow42]

Thanks for you help MajP

I'm not running a query, or at least I don't think I am. I am running the code for the find command ( the same as if you use the binocular icon). The only other thing is I have a filter, filtering on the OwnerDepot field (on the subform). I'm running this on access 2002.

The filter is set by a number of buttons one for each depot as follows

Private Sub Command40_Click()
Dim UserName As String
Dim UserDomain As String

UserName = Environ("USERNAME")
UserDomain = Environ("USERDOMAIN")

If DepotLoc.Caption = "Depot" Then

    CompUser.Caption = UserName
    DepotLoc.Caption = "Depot1"


    Forms!Main!DepotListSubform.Form.Filter = "[OwnerDepot]= 'Depot1'"
    Forms!Main!DepotListSubform.Form.FilterOn = True
    
    Me!Main.SetFocus
    
    On Error GoTo Err_Command14_Click


    'Screen.PreviousControl.SetFocus
    DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

Exit_Command14_Click:
    Exit Sub

Err_Command14_Click:
    MsgBox Err.Description
    Resume Exit_Command14_Click
    
    
    
Else
    MsgBox "You can only select a depot once!"
End If


End Sub

Then after the user enters a new location etc as above they press a find button to go to another part number.

Private Sub Command14_Click()

If DepotLoc.Caption = "Depot" Then

    MsgBox " Please select a depot First"

Else

ActStatus.Caption = "Status"

Me!Main.SetFocus

On Error GoTo Err_Command14_Click

    
    'Screen.PreviousControl.SetFocus
    DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

Exit_Command14_Click:
    Exit Sub

Err_Command14_Click:
    MsgBox Err.Description
    Resume Exit_Command14_Click
    
End If
End Sub

It may also be worth noting I split the part number into 3 a prefix, the main and extra. hence the reference above to main is the part number (I simplified this earlier - he say ha ha)

I guess when I can get my mind around this skys the limit.

Thanks again

 

[MajP]

I will try to dig in your code, to see if I can see anything. However, I still believe the problem is with the query that your subform uses. You may not be running a specific query, but usually any form is based on a query or a table. I believe in one of your queries you have something like

...WHERE [SomeFieldName] = DepotListSubform.Form.Part!...

That is why you get the prompt and the inability to requery that subform. If you can post the SQL of the mainform, and subforms it will help. You can probably get it right out of the forms "rowsource" property. But if the row source is a named query then would need to get the SQL using the link I showed.

You can also get a similar prompt with the filter if there is a problem with it. It looks correct. However, in design view check to make sure there is not already a filter set in the form and subform.

 

[Vsnow42]

Thanks MajP

The form and subform are based directly on Linked tables not a table as a result of a query or directly on a query itself. The Master table holds all the possible parts that any depot may have or get access to from another depot. Each depot ( on subform) can see the location (shelf) in their own depots (filter on depot)but not the other depots.

The Visual Basic code from the main form is as follows ( the link you provided above is for a much newer version of Access than the one I have, so could not get code that way). I have shortened the code a bit as I have a number of buttons with the same code but different depots - the code refering to depot1 and depot2 are repeated another 7 times.

I have searched my code for anything that looked like your example, but could not find anything. Any other ideas / thoughts on what I have done wrong.

Thanks again for your help.


Main form code:-

Option Compare Database

Private Sub ActStatus_Click()
MsgBox "You can not change this"
End Sub

Private Sub Command14_Click()

If DepotLoc.Caption = "Depot" Then

    MsgBox " Please select a depot First"

Else

ActStatus.Caption = "Status"

Me!Main.SetFocus

On Error GoTo Err_Command14_Click

    
    'Screen.PreviousControl.SetFocus
    DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

Exit_Command14_Click:
    Exit Sub

Err_Command14_Click:
    MsgBox Err.Description
    Resume Exit_Command14_Click
    
End If
End Sub
Private Sub Command30_Click()

    
End Sub


Private Sub Command40_Click()
Dim UserName As String
Dim UserDomain As String

UserName = Environ("USERNAME")
UserDomain = Environ("USERDOMAIN")

If DepotLoc.Caption = "Depot" Then

    CompUser.Caption = UserName
    DepotLoc.Caption = "Depot1"


    Forms!Main!DepotListSubform.Form.Filter = "[OwnerDepot]= ' Depot1'"
    Forms!Main!DepotListSubform.Form.FilterOn = True
    
    Me!Main.SetFocus
    
    On Error GoTo Err_Command14_Click


    'Screen.PreviousControl.SetFocus
    DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

Exit_Command14_Click:
    Exit Sub

Err_Command14_Click:
    MsgBox Err.Description
    Resume Exit_Command14_Click
    
    
    
Else
    MsgBox "You can only select a depot once!"
End If


End Sub


Private Sub Command47_Click()
Dim UserName As String
Dim UserDomain As String

UserName = Environ("USERNAME")
UserDomain = Environ("USERDOMAIN")

If DepotLoc.Caption = "Depot" Then

    CompUser.Caption = UserName
    DepotLoc.Caption = " Depot2"


    Forms!Main!DepotListSubform.Form.Filter = "[OwnerDepot]= ' Depot2'"
    Forms!Main!DepotListSubform.Form.FilterOn = True
    
    Me!Main.SetFocus
    
    On Error GoTo Err_Command14_Click


    'Screen.PreviousControl.SetFocus
    DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

Exit_Command14_Click:
    Exit Sub

Err_Command14_Click:
    MsgBox Err.Description
    Resume Exit_Command14_Click
    
    
    
Else
    MsgBox "You can only select a depot once!"
End If


End Sub


Private Sub CompUser_Click()
MsgBox "You can not change this"
End Sub

Private Sub DepotLoc_Click()

MsgBox "You can not change this"
End Sub

Private Sub Extra_Click()
If Me!Extra.Text = "" Then

    Else
MsgBox "You should not type in this box"
    End If
End Sub

Private Sub Form_Open(Cancel As Integer)
DoCmd.Maximize
End Sub

Private Sub Label59_Click()
MsgBox "You can not change this"
End Sub

Private Sub Label68_Click()
MsgBox "You can not change this"
End Sub

Private Sub Label73_Click()
MsgBox "You can not change this"
End Sub

Private Sub Main_Click()
    
 If Me.ActStatus.Caption = "Adding Tool to Master Parts List" Then
 
 Else
    If DepotLoc.Caption = "Depot" Then
        MsgBox "Select a depot first"
        Me!Prefix.SetFocus
    Else

    End If
End If
    
End Sub

Private Sub NewTool_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)

Me.Prefix.SetFocus
Me.ActStatus.Caption = "Adding Tool to Master Parts List"
Me.NewTool.SetFocus

End Sub

Private Sub Prefix_Click()
If Me!Prefix.Text = "" Then

    Else
MsgBox "You should not type in this box"
    End If
End Sub
Private Sub Exit_Tool_Click()
On Error GoTo Err_Exit_Tool_Click


    DoCmd.Quit

Exit_Exit_Tool_Click:
    Exit Sub

Err_Exit_Tool_Click:
    MsgBox Err.Description
    Resume Exit_Exit_Tool_Click
    
End Sub
Private Sub ExitTool_Click()
On Error GoTo Err_ExitTool_Click


    DoCmd.Quit

Exit_ExitTool_Click:
    Exit Sub

Err_ExitTool_Click:
    MsgBox Err.Description
    Resume Exit_ExitTool_Click
    
End Sub
Private Sub NewTool_Click()

On Error GoTo Err_NewTool_Click


    DoCmd.GoToRecord , , acNewRec

Exit_NewTool_Click:
    Exit Sub

Err_NewTool_Click:
    MsgBox Err.Description
    Resume Exit_NewTool_Click
    

    
End Sub
Private Sub NewToolFm_Click()
On Error GoTo Err_NewToolFm_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "MasterList"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_NewToolFm_Click:
    Exit Sub

Err_NewToolFm_Click:
    MsgBox Err.Description
    Resume Exit_NewToolFm_Click
    
End Sub

 

[MajP]

I cannot see anything glaring, but this is not easy to follow. The only thing I did see was you had spaces before your values. This may or may not be intentional and should not cause a crash.
"[OwnerDepot]= ' Depot1'"
should this be
"[OwnerDepot]= 'Depot1'"

That will make a difference if that is not what you intend.

As far as books. You can get a ton of access books online. I buy used books for vba and normally costs about a 1-5 dollars. VBA has changed very little so a Access 20003-2007 book would still be pretty relevant. The Access Desktop developers handbook is the best Access book I have read. It is a little advanced so you might want to get a beginners book first.
You can get it for 1.81 used

http://www.amazon.com/gp/offer-listing/0782140092/ref=dp_olp_used?ie=UTF8&condition=used

 

[Vsnow42]

Ok thanks for having a look and the book reference. It is a lot more money here in the UK, but if it contains good info then ok. I hate these books that promise the earth and tell you how to make a picture move, so thanks for the recommendation.

I suspect what I'm trying to do is simple and there is something I have yet to grasp.Simple linked tables, a master with a child and a child of the child. All with information which is automatically entered and some user input.

I may remove most of the code that revoles around stopping the user entering info where they should not and that may make it easier to find what it does not like.

Again thanks for taking time to look.

 

[TheAceMan1]

How are ya Vsnow42 . . .

DoCmd.DoMenuItem is legacy and for compatibility. Although I don't believe it'll make a difference, try the [blue]runcommand[/blue] (up to date) equivalent ... couldn't hurt:

[blue]   DoCmd.RunCommand acCmdFind[/blue]

See Ya . . .

Be sure to see FAQ219-2884 Worthy Reading!
Also FAQ181-2886 Worthy Reading!

 

[Vsnow42]

Hi all

Thanks for the suggestion TheAceMan1, I will try that in a little while, have only just seen it.

Though I think I have found the problem, but don't quite understand. The first part of the subform code above (copied below) seems to be causing the problem and if I remove it all seems to work - yahoo. The thing is I don't remember typing it and don't know what it is for? error code for some reason. I thought perhaps it was put on automatically, but the other subform does not have it? Any ideas?

Option Compare Database

Sub Form_Current()

    Dim ParentDocName As String

    On Error Resume Next
    ParentDocName = Me.Parent.Name

    If Err <> 0 Then
        GoTo Form_Current_Exit
    Else
        On Error GoTo Form_Current_Err
        Me.Parent![BookedSubform].Requery
    End If

Form_Current_Exit:
    Exit Sub

Form_Current_Err:
    MsgBox Error$
    Resume Form_Current_Exit

End Sub

 

[MajP]

If your subforms are linked to the main form then that code is redundant and probably a problem. With linked subforms they requery on the parents form current event automatically. If you do not link them but set the subform's query to reference a value on the main form in order to filter the subform, then you would need some code like this in order to requery the subform.

 

[TheAceMan1]

Vsnow42 . . .

The function of the code you posted (just above) appears to be to requery the [blue]subform BookedSubform[/blue] when you change records on the [blue]current subform[/blue] (where this code resides ... I think). All in all your synchronizing subforms here. Whatever error checking was meant, might as well be forgotton as [red]you exit the event if their is any error![/red]

Please let us know [blue]where the code resides[/blue] (its important where code is executing) and wether the intent of the code makes sense to you.

[blue]Your Thoughts? . . .[/blue]



See Ya . . .

Be sure to see FAQ219-2884 Worthy Reading!
Also FAQ181-2886 Worthy Reading!

 

[Vsnow42]

Hi TheAceMan!

The Code was on the subform form itself (Not on any buttons fields etc).

MajP
I created the form using the wizard, but then went on to alter the links manually in terms of the join types ( could this have been put in during the wizard use, but hen become redundant when I set the links myself ( problem is this error only came up several months after I created it as it was only being used by one depot (no filtering was taking place) and my memory is not as good as it used to be. Can't quite remember if I just set the links on the tables or on the form ( or both). Better go back and have a look this weekend. You may have guessed that this is not my main job ( so mostly evenings and weekends), but used to make my life easier ( real job is H&S Manager - but surprising how useful it is to be able to do this)

Its working now as I testing it quite extensively today ( with this code comma'd out - didn't want to zap it just yet)