Calcuated number

[jdbegg]

I'm stuck on a problem, not knowing the best way to handle it.

I have a form which reads one table. The table has two fields that I'm working with. One is a location and the other is a sequential number.

Currently, I have a combo box that contains the location from the table. I can step through the rows ok, but want to get the numerical value to increment by 1 automatically when I add a record for the location selected. As an example, lets say I have 5 records in the table for London. When I want to add another record for London I want to get the Max value of the number and add 1 to it. This number is not the Key to the table. I have the key set to automatically increment.

I created a Max Value Query that is working correctly, but don't know where to go from there. I am studying about Macros and was able to open the query, but don't know if I'm even heading in the right direction. Should I charge on with the macro or is there a better way to do this?

Help!!!

jdb

 

[pono1]

jdb,

Use code, the DMAX function, and your form's Before Update Event. Not that difficult.

As you probably know, Access is continually notifying you what it is doing. Responding to these notifications allows you to take control of your program.

For example, immediately before your form saves a record Access silently shouts out to you, "I'm about to save a record. Any last second requests?" You silently respond by putting code in the Before Update Event of your form. (No code means no answer and your form simply carries on with its duties.)

Private Sub Form_BeforeUpdate(Cancel As Integer)

' Instructions to Access: 
'  Always do the following immediately before you 
'  save a record using this form.

'Is this a new record?
	If Me.NewRecord = True Then 'then carry on.

'Create a container (a variable) in memory.
	Dim MyNextNumber as Long

'Fill the container using the DMAX function.
	MyNextNumber = DMAX("FieldToGet","TblName", _
			"SearchField=" & Forms!MyForm!txtBoxName) + 1

'Copy the variable's value to a bound textbox on your form.
	Forms!MyForm!TextBoxName = MyNextNumber

	End If

End Sub

Regards,
Tim

 

[jdbegg]

Tim,

No, I didn't know Access was doing all this stuff in the background. Thanks for the information and the code. I haven't gotten around to trying it yet, but will.

Thanks,

Jim

 

[Pat Hartman]

I would use the BeforeInsert event rather than BeforeUpdate since you only want this code to run when a NEW record is inserted. That way you don't need to test the NewRecord property.

 

[jdbegg]

I wish I knew more about the syntax of VB. but alas I don't...

I don't know what the underbar is for _

I'm having trouble with the code not working. I finally got it to compile.

Any help would be apprecaited.

'Is this a new record?
If Me.NewRecord = True Then 'then carry on.

'Create a container (a variable) in memory.
Dim MyNextNumber As Long

'Fill the container using the DMAX function.
MyNextNumber = DMax("[Action_Item_Number]", "Action_Item_Main_Table", _
"SearchField = " & Forms!Action_Item_Main_Form!Action_Item_Number) + 1

'Copy the variable's value to a bound textbox on your form.
Forms!Action_Item_Main_Form!Action_Item_Number = MyNextNumber

 

[pono1]

Jim,

The underscore (_) wraps a line for readability's sake. VBA still sees the line as one continuous segment.

The code looks OK (well commented too). Troubleshoot by verifying the field names in the DMAX function. Do you have a field named SearchField in the table Action_Item_Main_Table? And do you have a field named Action_Item_Number in the table Action_Item_Main_Table? If you answered "no" to one of these two questions, the fix should be pretty easy...

A little thing: It's customary -- though not compulsory -- to change the default names of the controls on a form. Most people, for example, preface their textboxes with a "Txt." So Action_Item_Number becomes, say, Txt_Action_Item_Number.

Regards,
Tim