By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,224 Members | 1,037 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,224 IT Pros & Developers. It's quick & easy.

DMax function Problem

P: 55
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
Sep 5 '07 #1
Share this Question
Share on Google+
2 Replies


P: 55
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
Sep 5 '07 #2

Jim Doherty
Expert 100+
P: 897
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeInsert(Cancel As Integer)
  2. 'I'll leave you to do your own error handling here
  3. dim mynewnum as long
  4. mynewnum= DMax("[JobID]","YourJobTable","[CustomerID]='" & Me.OpenArgs &")
  5. Me!JobID=mynewnum
  6. Me!CustomerID=Me.Openargs
  7.  
  8. End sub
  9.  

Regards

Jim
Sep 5 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.