subform works every other time

Z

ZigZagZak

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
 
Z

ZigZagZak

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

VBA Export to PDF 0
Macro to Select text from a point given to a specific word 2
Choose report does not work in 2007 0
DoCmd.OpenForm question 3
Word macro that can call cells out of Excel for a find and replace 0
Print multiple booklet pages on 1 sheet 0
VBA-Table Word 2019 - Move a selected text, from a cell to the adjacent cell to its right, by pressing the ENTER key 0
If, Then, Else 3

Top