OpenForm (2000)

Topic

New Reply

I have a form (continuous) that displays a series of truck loads that is used for billing verification. Two of the text boxes have DblClick Subs that take you to either the frmBillOut (to finalize the billing) or the frmOrderDetail (to edit the billing method). The “logic” behind this is that the frmBillOut is a very small form with limited info, and the frmOrderDetail is a full screen form with all of the data associated with the load (very intense). The first Sub is for the frmBillOut and works just fine:

Private Sub PRONo_DblClick (Cancel As Integer)
On Error GoTo Err_ViewOrder_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = “frmBillOut”

stLinkCriteria = ” [ProNo] =” & Me! [ProNo]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_ViewOrder_Click:
Exit Sub

Err_ViewOrder_Click:
MsgBox Err.Description
Resume Exit_ViewOrder_Click
End Sub
__________________________________________________________________________

The second Sub is for frmOrderDetail, and returns the error mess “The OpenForm action was canceled”. Here it is:

Private Sub Release1No_DblClick (Cancel As Integer)
On Error GoTo Err_ViewOrder_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = “frmOrderDetail”

stLinkCriteria = ” [Release1No] =” & Me! [Release1No]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_ViewOrder_Click:
Exit Sub

Err_ViewOrder_Click:
MsgBox Err.Description
Resume Exit_ViewOrder_Click
End Sub

I can’t see the difference between the two statements, but the second one returns the error message nevertheless. I DID place a cmdButton on the form with the same frmOrderDetail criteria and it works just fine, but I don’t want another cmdButton on the form. Anybody got any clues?

Reply | Quote

Replies

What code have you in the frmOrderDetail? Particularly in its Open event?

Reply | Quote

There are no event properties for frmOrderDetail

Reply | Quote

I FIXED IT
Thanks for your input! (please see thread)

Reply | Quote

I think there is a problem with *both* DblClick handlers. If the user changes the text in the text box and then double clicks, stLinkCondition uses the stored value of the text box, not the currently visible value, so there will be a discrepancy.

Insert the following code in both DblClick handlers before assigning a value to stLinkCriteria:

If Me.Dirty Then
RunCommand acCmdSaveRecord
End If

This will save the record if it has been changed, so that the stored and currently visible value of the text box are equal.

When you click a command button to open frmOrderDetail, you exit the Release1No text box, so its value is updated.Then, the stored and currently visible values are equal, so the problem I mentioned doesn’t occur.

Reply | Quote

Thanks Hans, it didn’t fix the current problem but I now understand how important that “Dirty” issue could be. As a newbie to VBA, I appreciate every bit of guidance.

Reply | Quote

The “ProNo” and “Release1No” are assigned at the time of order entry and should never be editied by the operator (if that makes any difference). The ProNo is an autonumber field and the Release1No never changes after the original entry. Not being real certain of what I’m doing here, I tried a little experiment:

I kept the code for “Release1No_DblClick” the same except I changed the stDocName to “frmBillOut” (seeing as that form opens without any problem from “ProNo_DblClick”). I get the same error message as before.

I changed the code back to what it originally was, added a cmdButton for frmOrderDetail, and it works just fine.

I suppose I can live with the cmdButton, but it’s inconsistant with the way operators attain information with other forms. The DblClick function is the way things are normally done. Also, and seeing as I AM a newbie, I want to understand what’s going wrong so I can gain some knowledge here. I just don’t get why a cmdButton (applied using a wizard) would work just fine, but the DblClick would fail; and especially in light of the fact that the dblClick from “ProNo” also works just fine when opening frmBillOut. I actually thought that David was on to something when he asked about the properties for frmOrderDetail, but seeing as it works just fine from the cmdButton, I’m not so sure that’s the issue either.

Reply | Quote

Are you sure that Release1No (spelled exactly that way) is a field in the record source of frmOrderDetail? If the field is absent there, or has a different name, you’d get an error message, since you have Release1No in the Where-condition of DoCmd.OpenForm.

Reply | Quote

I FIXED IT!

Original code:
stLinkCriteria = ” [Release1No] = ” & Me ! [Release1No]

Modified:
stLinkCriteria = ” [Release1No] = ” & ” ‘ ” & Me ! [Release1No] & ” ‘ ”

Before anyone thinks I’m a 52 year-old “boy” genious, I stole the code from the cmdButton. The only problem I have now is that I don’t know why the modified code works. I have corrected the problem, but I haven’t learned anything (other than how to “steal” code). I very much appreciate your input (especially concerning “Dirty”), would you mind explaining why the modified code works? Also, if you would be so kind, why the original code works just fine for ProNo_DblClick, but not for Release1No_DblClick? Should I also change the code for ProNo_DblClick (even though it IS working just fine)?

Thanks again for your generousity!

Reply | Quote

Apparently, ProNo is a numeric field and Release1No is a text field. If you want to specify a where-condition for a text field, you have to enclose the value in quotes (single works easiest in code).

Perhaps an example with constant values will clarify this:

ProNo = 39 is correct, as is Release1No = ‘NQ45’, but Release1No = NQ45 is incorrect. Since Release1No is text, Access will assume that NQ45 is the name of another field (or function), but won’t be able to find that.

Since your field names both end in No, I didn’t think of the possibility that one of them would be a text field; I had assumed that both were numeric.

Reply | Quote

Boy, what an adventure! It’s difficult enough stumbling around in the jungle watching for snakes, but doing it without a compass is nearly impossible!

I went back and looked at the tblMaster where the forms get their data and discovered (as you have pointed out) that the Release1No is, indeed, a text field. I also discovered, as I was about to change it to a numeric field, that this number is given to us by the shipper and “may” contain text as well; whereas the ProNo is an in-house number (auto). The field “name” is misleading (as you also pointed out) and I am considering changing it, but it is used in so many different references that it may not be worth the effort.

I have learned a few valuable lessons here. Naming conventions can be (are) critical to the reading of code, both for the user and the creator. I have purchased several books on Access and, recently, VBA; but having the text doesn’t necessarily mean that you will even be able to locate the answers (it’s a big jungle). I am discovering the value of being able to write code, and obviously the dismay associated with what only appear as “minor” errors. I am most grateful for resolving the problem, but even more so for having learned something new that I can take with me into the jungle.

I don’t want to get all emotional here, but this is one VERY grateful newbie. Not only were you willing to address the immediate problem, but were willing to go the extra mile in explaining the code. Thanks for providing a compass!

Reply | Quote