DMax function Problem

[ontherun]

Hi,

I have a form called Customer based on the table tblCustomer and
another form called Job based on the table Job

when the user enter the details for the Customer i have a button at the bottm of the form called EnterJob where it directs to the Job Form

I have included some code in EnterJob button so when the user clicks, it gets the value for Customer Name and Max value of the Job number and enter into the appropriate field in the Job form.. Code is :

Private Sub EnterJobLabel_Click()

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

DoCmd.OpenForm "New_Job"
DoCmd.GoToRecord , , acNewRec

Forms!New_Job!Company_Name.Value = Forms!New_Customer!Company_Name.Value


Forms!New_Job!Job_No = DMax("Job_No", "CHS_Job") + 1

End Sub


I dont have problem to get the company name from the current form and palce it to the job form..
but i cant get the Max number for Job_No .
what i am getting is an empty field.

I think Dmax function works fine but it is not assigning the value into the Job_No field in Job Form

could anyone tell me what i have done wrong???

regards,
Catherine

[ontherun]

Hi,

I have a form called Customer based on the table tblCustomer and
another form called Job based on the table Job

when the user enter the details for the Customer i have a button at the bottm of the form called EnterJob where it directs to the Job Form

I have included some code in EnterJob button so when the user clicks, it gets the value for Customer Name and Max value of the Job number and enter into the appropriate field in the Job form.. Code is :

Private Sub EnterJobLabel_Click()

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

DoCmd.OpenForm "New_Job"
DoCmd.GoToRecord , , acNewRec

Forms!New_Job!Company_Name.Value = Forms!New_Customer!Company_Name.Value


Forms!New_Job!Job_No = DMax("Job_No", "CHS_Job") + 1

End Sub


I dont have problem to get the company name from the current form and palce it to the job form..
but i cant get the Max number for Job_No .
what i am getting is an empty field.

I think Dmax function works fine but it is not assigning the value into the Job_No field in Job Form

could anyone tell me what i have done wrong???

regards,
Catherine

Sorry guys.
I got that working...

i declared the field as Text .silly me...
only now i noticed it.

I changed it to number and its all working.. i would hav gone with autonumber but i want to get the max number through VBA

regards,
Catherine

[Jim Doherty]

Sorry guys.
I got that working...

i declared the field as Text .silly me...
only now i noticed it.

I changed it to number and its all working.. i would hav gone with autonumber but i want to get the max number through VBA

regards,
Catherine

Just an observation Catherine... if your db is used by you only then fine, but if you intend to use it in a multi user environment you might want to revisit the design on the way you are passing that value over?.

It is possible in a MU environment using that method, that 'someone else' will grab the number you have passed over statically to the job form and use it to insert a job before you had the chance to do so. Potentially when you come to applying the Dmax number you requested as a new record you'll get a 'Duplicates not allowed' message.

You might want to look at the BeforeInsert event of the Job form to grab the latest number for your job and base the Customer to whom it applies as the value of the 'Openargs' of the form?
where openargs 'value' is passed over to jobs form as part of the DoCmd.Openform arguments command...... from the customers form.

Private Sub Form_BeforeInsert(Cancel As Integer)
'I'll leave you to do your own error handling here
dim mynewnum as long
mynewnum= DMax("[JobID]","YourJobTable","[CustomerID]='" & Me.OpenArgs &")
Me!JobID=mynewnum
Me!CustomerID=Me.Openargs
 
End sub
 

Regards

Jim