subform works every other time

G

Guest

Hey. I have a data entry form that has a subform based on a query. The
subforms query is based on a table that holds the cpsi (cost per sq. inch) of
our steel plate stock. What its supposed to do is, when you pick a size of
plate to enter, it tags the entry with its cost per inch. It works, however
I wrote a code to make switching from size to size easier, and now it only
works every other time. It seems to be some sort of refresh problem. But
when I put me.refresh into the code it errors out with "The expression you
entered refers to an object that is closed or doesn't exist." Below is my VB
code.......help!!!!!

Private Sub Command47_Click()
Dim stDocName As String
Dim stLinkCriteria As String


Forms![PLATE SELECTION].Visible = True
If Forms![PLATE SELECTION].THICKNESSFRM = "7/32" Then
Forms![PLATE SELECTION].THICKNESSFRM = "9/32"
DoCmd.Close acForm, "PLATE ENTRY"
stDocName = "PLATE ENTRY"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Forms![PLATE SELECTION].Visible = False
Forms![PLATE ENTRY].Visible = True
Exit Sub
End If
If Forms![PLATE SELECTION].THICKNESSFRM = "9/32" Then
Forms![PLATE SELECTION].THICKNESSFRM = "5/16"
DoCmd.Close acForm, "PLATE ENTRY"
stDocName = "PLATE ENTRY"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Forms![PLATE SELECTION].Visible = False
Forms![PLATE ENTRY].Visible = True
Exit Sub
End If
If Forms![PLATE SELECTION].THICKNESSFRM = "5/16" Then
Forms![PLATE SELECTION].THICKNESSFRM = "7/16"
DoCmd.Close acForm, "PLATE ENTRY"
stDocName = "PLATE ENTRY"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Forms![PLATE SELECTION].Visible = False
Forms![PLATE ENTRY].Visible = True
Exit Sub
End If
If Forms![PLATE SELECTION].THICKNESSFRM = "7/16" Then
Forms![PLATE SELECTION].THICKNESSFRM = "9/16"
DoCmd.Close acForm, "PLATE ENTRY"
stDocName = "PLATE ENTRY"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Forms![PLATE SELECTION].Visible = False
Forms![PLATE ENTRY].Visible = True
Exit Sub
End If
If Forms![PLATE SELECTION].THICKNESSFRM = "9/16" Then
Forms![PLATE SELECTION].THICKNESSFRM = "21/32"
DoCmd.Close acForm, "PLATE ENTRY"
stDocName = "PLATE ENTRY"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Forms![PLATE SELECTION].Visible = False
Forms![PLATE ENTRY].Visible = True
Exit Sub
End If
If Forms![PLATE SELECTION].THICKNESSFRM = "21/32" Then
Forms![PLATE SELECTION].THICKNESSFRM = "3/4"
DoCmd.Close acForm, "PLATE ENTRY"
stDocName = "PLATE ENTRY"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Forms![PLATE SELECTION].Visible = False
Forms![PLATE ENTRY].Visible = True
Exit Sub
End If
If Forms![PLATE SELECTION].THICKNESSFRM = "3/4" Then
Forms![PLATE SELECTION].THICKNESSFRM = "7/8"
DoCmd.Close acForm, "PLATE ENTRY"
stDocName = "PLATE ENTRY"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Forms![PLATE SELECTION].Visible = False
Forms![PLATE ENTRY].Visible = True
Exit Sub
End If
If Forms![PLATE SELECTION].THICKNESSFRM = "7/8" Then
Forms![PLATE SELECTION].THICKNESSFRM = "1"
DoCmd.Close acForm, "PLATE ENTRY"
stDocName = "PLATE ENTRY"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Forms![PLATE SELECTION].Visible = False
Forms![PLATE ENTRY].Visible = True
Exit Sub
End If
If Forms![PLATE SELECTION].THICKNESSFRM = "1" Then
Forms![PLATE SELECTION].THICKNESSFRM = "1 1/8"
DoCmd.Close acForm, "PLATE ENTRY"
stDocName = "PLATE ENTRY"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Forms![PLATE SELECTION].Visible = False
Forms![PLATE ENTRY].Visible = True
Exit Sub
End If
If Forms![PLATE SELECTION].THICKNESSFRM = "1 1/8" Then
Forms![PLATE SELECTION].THICKNESSFRM = "1 1/4"
DoCmd.Close acForm, "PLATE ENTRY"
stDocName = "PLATE ENTRY"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Forms![PLATE SELECTION].Visible = False
Forms![PLATE ENTRY].Visible = True
Exit Sub
End If
If Forms![PLATE SELECTION].THICKNESSFRM = "1 1/4" Then
Forms![PLATE SELECTION].THICKNESSFRM = "1 3/8"
DoCmd.Close acForm, "PLATE ENTRY"
stDocName = "PLATE ENTRY"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Forms![PLATE SELECTION].Visible = False
Forms![PLATE ENTRY].Visible = True
Exit Sub
End If
If Forms![PLATE SELECTION].THICKNESSFRM = "1 3/8" Then
Forms![PLATE SELECTION].THICKNESSFRM = "1 1/2"
DoCmd.Close acForm, "PLATE ENTRY"
stDocName = "PLATE ENTRY"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Forms![PLATE SELECTION].Visible = False
Forms![PLATE ENTRY].Visible = True
Exit Sub
End If
If Forms![PLATE SELECTION].THICKNESSFRM = "1 1/2" Then
Forms![PLATE SELECTION].THICKNESSFRM = "1 3/4"
DoCmd.Close acForm, "PLATE ENTRY"
stDocName = "PLATE ENTRY"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Forms![PLATE SELECTION].Visible = False
Forms![PLATE ENTRY].Visible = True
Exit Sub
End If
If Forms![PLATE SELECTION].THICKNESSFRM = "1 3/4" Then
Forms![PLATE SELECTION].THICKNESSFRM = "2 1/4"
DoCmd.Close acForm, "PLATE ENTRY"
stDocName = "PLATE ENTRY"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Forms![PLATE SELECTION].Visible = False
Forms![PLATE ENTRY].Visible = True
Exit Sub
End If
If Forms![PLATE SELECTION].THICKNESSFRM = "2 1/4" Then
Forms![PLATE SELECTION].THICKNESSFRM = "2 3/4"
DoCmd.Close acForm, "PLATE ENTRY"
stDocName = "PLATE ENTRY"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Forms![PLATE SELECTION].Visible = False
Forms![PLATE ENTRY].Visible = True
Exit Sub
End If
If Forms![PLATE SELECTION].THICKNESSFRM = "2 3/4" Then
Forms![PLATE SELECTION].THICKNESSFRM = "3"
DoCmd.Close acForm, "PLATE ENTRY"
stDocName = "PLATE ENTRY"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Forms![PLATE SELECTION].Visible = False
Forms![PLATE ENTRY].Visible = True
Exit Sub
End If
If Forms![PLATE SELECTION].THICKNESSFRM = "3" Then
Forms![PLATE SELECTION].THICKNESSFRM = "3 1/2"
DoCmd.Close acForm, "PLATE ENTRY"
stDocName = "PLATE ENTRY"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Forms![PLATE SELECTION].Visible = False
Forms![PLATE ENTRY].Visible = True
Exit Sub
End If
If Forms![PLATE SELECTION].THICKNESSFRM = "3 1/2" Then
Forms![PLATE SELECTION].THICKNESSFRM = "4 1/2"
DoCmd.Close acForm, "PLATE ENTRY"
stDocName = "PLATE ENTRY"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Forms![PLATE SELECTION].Visible = False
Forms![PLATE ENTRY].Visible = True
Exit Sub
End If
If Forms![PLATE SELECTION].THICKNESSFRM = "4 1/2" Then
Forms![PLATE SELECTION].THICKNESSFRM = "5 1/4"
DoCmd.Close acForm, "PLATE ENTRY"
stDocName = "PLATE ENTRY"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Forms![PLATE SELECTION].Visible = False
Forms![PLATE ENTRY].Visible = True

Exit Sub
End If
If Forms![PLATE SELECTION].THICKNESSFRM = "5 1/4" Then
Forms![PLATE SELECTION].THICKNESSFRM = "6 1/4"
DoCmd.Close acForm, "PLATE ENTRY"
stDocName = "PLATE ENTRY"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Forms![PLATE SELECTION].Visible = False
Forms![PLATE ENTRY].Visible = True
Exit Sub
End If
Me.Refresh
End Sub
 
G

Guest

Thanks for anyone who tried to figure this out, but I just did. I had my
record lock set to all records...changed it to no locks and it is working
perfectly now.

ZigZagZak said:
Hey. I have a data entry form that has a subform based on a query. The
subforms query is based on a table that holds the cpsi (cost per sq. inch) of
our steel plate stock. What its supposed to do is, when you pick a size of
plate to enter, it tags the entry with its cost per inch. It works, however
I wrote a code to make switching from size to size easier, and now it only
works every other time. It seems to be some sort of refresh problem. But
when I put me.refresh into the code it errors out with "The expression you
entered refers to an object that is closed or doesn't exist." Below is my VB
code.......help!!!!!

Private Sub Command47_Click()
Dim stDocName As String
Dim stLinkCriteria As String


Forms![PLATE SELECTION].Visible = True
If Forms![PLATE SELECTION].THICKNESSFRM = "7/32" Then
Forms![PLATE SELECTION].THICKNESSFRM = "9/32"
DoCmd.Close acForm, "PLATE ENTRY"
stDocName = "PLATE ENTRY"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Forms![PLATE SELECTION].Visible = False
Forms![PLATE ENTRY].Visible = True
Exit Sub
End If
If Forms![PLATE SELECTION].THICKNESSFRM = "9/32" Then
Forms![PLATE SELECTION].THICKNESSFRM = "5/16"
DoCmd.Close acForm, "PLATE ENTRY"
stDocName = "PLATE ENTRY"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Forms![PLATE SELECTION].Visible = False
Forms![PLATE ENTRY].Visible = True
Exit Sub
End If
If Forms![PLATE SELECTION].THICKNESSFRM = "5/16" Then
Forms![PLATE SELECTION].THICKNESSFRM = "7/16"
DoCmd.Close acForm, "PLATE ENTRY"
stDocName = "PLATE ENTRY"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Forms![PLATE SELECTION].Visible = False
Forms![PLATE ENTRY].Visible = True
Exit Sub
End If
If Forms![PLATE SELECTION].THICKNESSFRM = "7/16" Then
Forms![PLATE SELECTION].THICKNESSFRM = "9/16"
DoCmd.Close acForm, "PLATE ENTRY"
stDocName = "PLATE ENTRY"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Forms![PLATE SELECTION].Visible = False
Forms![PLATE ENTRY].Visible = True
Exit Sub
End If
If Forms![PLATE SELECTION].THICKNESSFRM = "9/16" Then
Forms![PLATE SELECTION].THICKNESSFRM = "21/32"
DoCmd.Close acForm, "PLATE ENTRY"
stDocName = "PLATE ENTRY"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Forms![PLATE SELECTION].Visible = False
Forms![PLATE ENTRY].Visible = True
Exit Sub
End If
If Forms![PLATE SELECTION].THICKNESSFRM = "21/32" Then
Forms![PLATE SELECTION].THICKNESSFRM = "3/4"
DoCmd.Close acForm, "PLATE ENTRY"
stDocName = "PLATE ENTRY"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Forms![PLATE SELECTION].Visible = False
Forms![PLATE ENTRY].Visible = True
Exit Sub
End If
If Forms![PLATE SELECTION].THICKNESSFRM = "3/4" Then
Forms![PLATE SELECTION].THICKNESSFRM = "7/8"
DoCmd.Close acForm, "PLATE ENTRY"
stDocName = "PLATE ENTRY"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Forms![PLATE SELECTION].Visible = False
Forms![PLATE ENTRY].Visible = True
Exit Sub
End If
If Forms![PLATE SELECTION].THICKNESSFRM = "7/8" Then
Forms![PLATE SELECTION].THICKNESSFRM = "1"
DoCmd.Close acForm, "PLATE ENTRY"
stDocName = "PLATE ENTRY"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Forms![PLATE SELECTION].Visible = False
Forms![PLATE ENTRY].Visible = True
Exit Sub
End If
If Forms![PLATE SELECTION].THICKNESSFRM = "1" Then
Forms![PLATE SELECTION].THICKNESSFRM = "1 1/8"
DoCmd.Close acForm, "PLATE ENTRY"
stDocName = "PLATE ENTRY"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Forms![PLATE SELECTION].Visible = False
Forms![PLATE ENTRY].Visible = True
Exit Sub
End If
If Forms![PLATE SELECTION].THICKNESSFRM = "1 1/8" Then
Forms![PLATE SELECTION].THICKNESSFRM = "1 1/4"
DoCmd.Close acForm, "PLATE ENTRY"
stDocName = "PLATE ENTRY"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Forms![PLATE SELECTION].Visible = False
Forms![PLATE ENTRY].Visible = True
Exit Sub
End If
If Forms![PLATE SELECTION].THICKNESSFRM = "1 1/4" Then
Forms![PLATE SELECTION].THICKNESSFRM = "1 3/8"
DoCmd.Close acForm, "PLATE ENTRY"
stDocName = "PLATE ENTRY"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Forms![PLATE SELECTION].Visible = False
Forms![PLATE ENTRY].Visible = True
Exit Sub
End If
If Forms![PLATE SELECTION].THICKNESSFRM = "1 3/8" Then
Forms![PLATE SELECTION].THICKNESSFRM = "1 1/2"
DoCmd.Close acForm, "PLATE ENTRY"
stDocName = "PLATE ENTRY"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Forms![PLATE SELECTION].Visible = False
Forms![PLATE ENTRY].Visible = True
Exit Sub
End If
If Forms![PLATE SELECTION].THICKNESSFRM = "1 1/2" Then
Forms![PLATE SELECTION].THICKNESSFRM = "1 3/4"
DoCmd.Close acForm, "PLATE ENTRY"
stDocName = "PLATE ENTRY"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Forms![PLATE SELECTION].Visible = False
Forms![PLATE ENTRY].Visible = True
Exit Sub
End If
If Forms![PLATE SELECTION].THICKNESSFRM = "1 3/4" Then
Forms![PLATE SELECTION].THICKNESSFRM = "2 1/4"
DoCmd.Close acForm, "PLATE ENTRY"
stDocName = "PLATE ENTRY"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Forms![PLATE SELECTION].Visible = False
Forms![PLATE ENTRY].Visible = True
Exit Sub
End If
If Forms![PLATE SELECTION].THICKNESSFRM = "2 1/4" Then
Forms![PLATE SELECTION].THICKNESSFRM = "2 3/4"
DoCmd.Close acForm, "PLATE ENTRY"
stDocName = "PLATE ENTRY"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Forms![PLATE SELECTION].Visible = False
Forms![PLATE ENTRY].Visible = True
Exit Sub
End If
If Forms![PLATE SELECTION].THICKNESSFRM = "2 3/4" Then
Forms![PLATE SELECTION].THICKNESSFRM = "3"
DoCmd.Close acForm, "PLATE ENTRY"
stDocName = "PLATE ENTRY"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Forms![PLATE SELECTION].Visible = False
Forms![PLATE ENTRY].Visible = True
Exit Sub
End If
If Forms![PLATE SELECTION].THICKNESSFRM = "3" Then
Forms![PLATE SELECTION].THICKNESSFRM = "3 1/2"
DoCmd.Close acForm, "PLATE ENTRY"
stDocName = "PLATE ENTRY"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Forms![PLATE SELECTION].Visible = False
Forms![PLATE ENTRY].Visible = True
Exit Sub
End If
If Forms![PLATE SELECTION].THICKNESSFRM = "3 1/2" Then
Forms![PLATE SELECTION].THICKNESSFRM = "4 1/2"
DoCmd.Close acForm, "PLATE ENTRY"
stDocName = "PLATE ENTRY"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Forms![PLATE SELECTION].Visible = False
Forms![PLATE ENTRY].Visible = True
Exit Sub
End If
If Forms![PLATE SELECTION].THICKNESSFRM = "4 1/2" Then
Forms![PLATE SELECTION].THICKNESSFRM = "5 1/4"
DoCmd.Close acForm, "PLATE ENTRY"
stDocName = "PLATE ENTRY"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Forms![PLATE SELECTION].Visible = False
Forms![PLATE ENTRY].Visible = True

Exit Sub
End If
If Forms![PLATE SELECTION].THICKNESSFRM = "5 1/4" Then
Forms![PLATE SELECTION].THICKNESSFRM = "6 1/4"
DoCmd.Close acForm, "PLATE ENTRY"
stDocName = "PLATE ENTRY"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Forms![PLATE SELECTION].Visible = False
Forms![PLATE ENTRY].Visible = True
Exit Sub
End If
Me.Refresh
End Sub
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

DoCmd.OpenForm question 3
debug error 1
Creating multiple spreadsheets from form in access. 2
Turn subform visible and pass criteria 6
Help with VBA code on a form 4
If, Then, Else 3
Error Message if no selection is made in List Box 2
ByPass key ms access 2016 0

Top