Go To Record Problem (1 Viewer)

[123dstreet]

Hi all. I have an issue I don't even know where to start. In my sales order form, there is a combo box to select each Part, currently, if a specific part is selected (after update) then I have this code; what it does is goes to the LAST recorded Serial Number in the database.

BT_Serialize:
    M = MsgBox("You are about to serialize " & Me![Qty] & " units of " & Me.Part_No & ". Do you want to proceed?", vbYesNo, "Serialization")
    If M = vbNo Then GoTo ExitThis
    If IsNull(Me![SNList]) = True Then
    
        QMax = Me![Qty]
        For Q = 1 To QMax
        stDocName = "BTSN (Form)"
        'stLinkCriteria = "[SOrderID] =" & Forms![S-Order (Form)].[Order ID]
        DoCmd.OpenForm stDocName, , , stLinkCriteria
       [COLOR=red] DoCmd.GoToRecord , , acLast[/COLOR]
        N = Forms![BTSN (Form)]![BT100Serial]
       [COLOR=red] DoCmd.GoToRecord , , acNewRec
[/COLOR]        N = N + 1
        stSNTextList = stSNTextList & "  " & N
        Forms![BTSN (Form)]![BT100Serial] = N
        Forms![BTSN (Form)]![Part] = Me![Part No]
        Forms![BTSN (Form)]![SOrderID] = Forms![S-Order (Form)].[Order ID]
        'Forms![BTSN (Form)]![Cus Name] = DLookup("[Cus Name]", "Customer", "[Cus ID]=[Cus ID]")
        'Add field to form here
    
        Me![SNList] = stSNTextList
        Refresh
        Next Q
    DoCmd.Close acForm, stDocName
    GoTo ExitThis
    
    Else
    MsgBox ("You already generated Serial Numbers for this line item")
    'Refresh
    End If

Our current product will follow the same serial numbers, 3001, 3002, 3003... etc. our new product will start at 5000, 5001, 5002... etc. Now, my problem is when the current product is selected, how do I get it to refer back to the 3000's instead of going to the last record, which is 5000... any suggestions would be greatly appreciated. Also, the new product is almost the same as the current one, so it has to remain in the same table as the current.

 

[KenHigg]

I'm not sure I fully understand what you are trying to do but it seems like you should capture the record number you want to go back to before you move off of it...

 

[123dstreet]

Basically the Serial Number is the PK. It is not autonumber or anything. So if (After Update) the product is "ABC" then I want to find the last record in the 3000's and then create the next sequential serial number. if the product is "JKL" then i want it to go to the LAST record which will be in the 5000's.

 

[KenHigg]

So using this logic you will only be able to have 2000 serial numbers for product 'ABC'?

 

[123dstreet]

Yes that is correct, but not a problem. But is there a code to get me back down to the 3000's and then use the next sequential number?

 

[sonof27]

Not sure if this would work but you could try to use a where clause within your line DoCmd.OpenForm stDocName, , , stLinkCriteria you seem to have turned it off in your code sample.

Set the strWhere to filter just the 3000 series if "ABC" is selected or 5000 series if "JKL" is selected.

Something like this, rough and not tested.

if myComboBox = "ABC" then
  strWhere = "SerialNumber like '????3*' "   ' here I am saying the 5th caracter is a 3 you need to change to suit.
else
  strWhere = "SerialNumber like '????5*' "  ' same here Serial Number would be your serial number column name
end if
 
DoCmd.OpenForm stDocName, , , strWhere

 

[SpentGeezer]

Greetings, Not sure if this will help...

You said instead of going to the last record, so I assume you want to add a new 3000 number (if the last one was 3456, then the new serial will be 3457). Or I am way off the mark.

dim rd_set
dim num_Serial
num_serial =0

Set Rd_Set = currentdb.openrecordset("<tablename>")
rd_set.movefirst
do until rd_set.eof

   if left(str(rd_set!<FieldName>,1)) = "3" then
      if rd_set!<fieldname > num_serial then
         num_Serial = rd_set!<fieldname>
      end if
   end if

   rd_set.movenext
loop

num_Serial = num_Serial + 1 'the new, latest serial number...