New Record Add "1" (1 Viewer)

[DLF]

Hi everybody,

I've fairly new to access and don't have a CLUE where to begin with this - I'm more than willing to do some research but I don't even know what to look for.

I have a tblOrders; tblAccounts

In my form I have a combobox to select the "account" number assigned to the order. Each order has a order number assigned sequentially depending on which account number is choosen. This is currently a manual logged process, which is crazy when I know it can be automated. I'd like to find the highest order number based on the account choosen and then autofill the next sequential number.

Where to begin?

Thank you all for your guidance!!!!

 

[ghudson]

Search for there are plenty of examples. It all starts with the DMax() function.

 

[godofhell]

DLF, try looking at the attached file. It increments the weekperiod depending on the week number entered. Should be pretty simple to understand.

 

[DLF]

I guess that is my first question - should I base this off a query or can it be calculated directly into the field on my form as it all resides in the same table that the form is based on?

The form is based on tblRequisitions..........
I want to select AcctNumber [combobox76] and return the max ReqNumber (field) found for that particular AcctNumber and add "1".

Here's what I have in checking out DMax:

In the [ReqNumber] properties I have the Control Source expression set to this:

=DMax("[ReqNumber]","tblRequisitions","[Combo76]")+1

However, I must have something wrong in the syntax because it IS returning the highest req number + "1" that it's finding in the entire tblRequisitions but ignoring the Combo76 criteria (where you select the AcctNumber).

????????

 

[godofhell]

DLF, you can use Dmax to achive the same results notice that you must check for Null value since the 1st entry for a new week will not return any records from the table and will return Null, you will get an error "invalid use of Null". Checking for NULL allows to enter 1 on the week period so that the next time the Dmax is called it will return 1 and you add 1 to that to write 2. I added a new text box for show but the same value is written to the weekperiod textbox. I have attached an updated database. Below is the code doing all the work. Change this line on your side "tblweeks", "WeekNumber = " & WeekNumber.Value to read "tblweeks", "WeekNumber = " & Combo76.Value

Private Sub WeekNumber_AfterUpdate()
Dim DmaxReturn As Integer

If IsNull(DMax("WeekPeriod", "tblweeks", "WeekNumber = " & WeekNumber.Value)) Then
Me.txtDmax = 1
Me.WeekPeriod.Value = 1
Else
DmaxReturn = DMax("WeekPeriod", "tblweeks", "WeekNumber = " & WeekNumber.Value)
Me.txtDmax = DmaxReturn + 1
Me.WeekPeriod.Value = DmaxReturn + 1
End If

DoCmd.RunCommand acCmdRefresh

'Old Code for achiving the same results using a hidden form to check against.
'DoCmd.Close acForm, "frmWeekPeriod"
'DoCmd.OpenForm "frmWeekPeriod", acNormal, , WeekNumber = Me.WeekNumber, acFormReadOnly, acHidden

End Sub

 

[DLF]

First tried your code and found it was returning all 1's for the ReqNumber field - strange I didn't vary much from your code. Here's what I have:

Dim DmaxReturn As Integer

If IsNull(DMax("ReqNumber", "tblRequisitions", "AcctNumber = " & AcctNumber.Value)) Then
Me.ReqNumber.Value = 1
Else
DmaxReturn = DMax("ReqNumber", "tblRequisitions", "AcctNumber = " & AcctNumber.Value)
Me.ReqNumber.Value = DmaxReturn + 1
End If

DoCmd.RunCommand acCmdRefresh

Then in testing I recognized another issue (one thing always leads to another doesn't it?), when selecting the various AcctNumbers from the combobox (from tblAccounts) I got a runtime error on AcctNumbers that it didn't find already in the tblRequisitions. The AcctNumber field that I am assigning to the form would not be found in the tblRequisitions if it was the first entry for that AcctNumber - therefore the ReqNumber should default to 001 if AcctNumber in tblRequisitions is null. After that, all ReqNumbers would be DMax + 1. So, I wouldn't be coding IsNull on the same DMax field as in your example....... this is WAY over my head.........

 

[godofhell]

DLF, as you can clearly see these things grow branches of their own. The query scenerio I had previously provided would probably be the easiest for you to visually see since all I was doing then was opening a form with the query results and comparing the values returned in the forms fields with one another. Doing it that way you can clearly see what the query has returned and then simply writing code to check if the value on the account number of the verify form is null, meaning there is no account in the database, then your account number = "001". Make sure to write it as "001" otherwise access removes the 00 from the number and makes it 1.