Navigate Form's Recordset (1 Viewer)

watrout · Mar 18, 2004

Need to navigate records on the form behind my current form

I'm working with 2 forms - "Purchase Orders" and "Product List." The "Product List" form is a pop-up form that is initiated from the "Purchase Order" form. I want the ProductID that I select from the product list form to be inserted into the next available field in the purchase order form (actually it's a subform within the purchase order form). I have everything working except checking to see if the current record is blank, and if it is not blank to insert the information in a new record. Here's the code:

Code:

Private Sub ProductList_DblClick(Cancel As Integer)
Dim intProductID As Integer
intProductID = Me.ProductList
Forms![Purchase Orders].SetFocus
Forms![Purchase Orders]![Purchase Orders Subform].SetFocus
[COLOR=Red]'DoCmd.GoToRecord acDataForm, Forms![Purchase Orders]![Purchase Orders Subform]![ProductID], acLast[/COLOR]
'The go-to-last record command is not working
    If Forms![Purchase Orders]![Purchase Orders Subform]![ProductID] = Not Null Then
        DoCmd.GoToRecord , , acNext
    End If
Forms![Purchase Orders]![Purchase Orders Subform]![ProductID] = intProductID
Forms!frmProductPickFromList.SetFocus
DoCmd.Close
End Sub

The line that is commented out is the statement I'm having problems with. I originally wrote it as: DoCmd.GoToRecord, , acLast - however, when I did that it took me to the last Purchase order record instead of the last record on the purchase order subform.

Thanks in advance for the help

Pat Hartman · Mar 19, 2004

1. I don't understand why you are using a pop-up form for this. Every other order entry application that I have seen, uses a combo or something along those lines.
2. If Forms![Purchase Orders]![Purchase Orders Subform]![ProductID] = Not Null will not correctly identify null values. The statement should be - If Not IsNull(Forms![Purchase Orders]![Purchase Orders Subform]![ProductID]) Search for help on Null to understand why you cannot use = null or = not null to identify null values.
3. If you insist on doing this with a pop-up, I think you always want to go to a new record on the previous form. I don't think you ever want to go to the last record.

watrout · Mar 19, 2004

Pat,
#1 - Yes, I agree and I also have a combo-box on my subform that functions just as you say. The problem comes when you have a supplier that has several hundred different products available. The pop-up form is simply a product list form that the user can apply filters to. So if it's an item we've ordered before the user will most likely know that it's in the computer. They may however, not know exactly how the product id was entered when it was ordered the first time. In this case they could hit the pop-up product catalog button that's on the form and search for the item via the category that the product fits in, or they can confine the list to only items that we carry in stock, ect. Additionally, we have several different vendors that carry similar products. The user might think it was ordered from one supplier, but they can't find it on that supplier's list...they can change the vendor on the pop-up form and see which vendor it is stored with. Lastly they can do a text search via any valid field associated with the product in question. So those are the reasons I developed the pop-up box.

#2 - You are right on that one. I was using an improper structure to test for Null. I had been trying several different way of writing this sub to see if I could get it to work and the one I posted was probably the worst of any configuration I've tried. This one I think is the closest I've come, but it still doesn't work:

Code:

Private Sub ProductList_DblClick(Cancel As Integer)
Dim intProductID As Integer
intProductID = Me.ProductList
Forms![Purchase Orders].SetFocus
Forms![Purchase Orders]![Purchase Orders Subform]![ProductID].SetFocus
    If Not IsNull(Forms![Purchase Orders]![Purchase Orders Subform]![ProductID]) Then
        DoCmd.GoToRecord , , acNew
    End If
Forms![Purchase Orders]![Purchase Orders Subform]![ProductID] = intProductID
Forms!frmProductPickFromList.SetFocus
DoCmd.Close
End Sub

When run this code I get a run time error #2105 stating that I can't go to the specified record. So any suggestions on how to remedy that would be appreciated.
#3 - The reason I'm testing for null here is: since the productid gets recorded in the subform there is alway at least 1 record present, even if that record is empty (thus null). Additionally, I have the button to initiate the pop-up form located on the purchase order main form. So it is possible to have several products already recorded on the purchase order subform. If the pop-up form is initiated from one of these existing records the return value overwrites that record. If the focus is on one of the main form's fields, the first record in the subform is overwritten. Therefore I wanted to check for null with respect to the current record and go to a new record if the record is not null. Another possibilty would be to only enable the button if the focus is on an empty record, but I'm not sure how to do that. Mainly because you would have to #1 disable it if the focus is anywhere on the main form and #2 disable it if the current record was not null.

Navigate Form's Recordset (1 Viewer)

watrout

Registered User.

Pat Hartman

Super Moderator

watrout

Registered User.

Similar threads

Users who are viewing this thread