new form 1

[paulbol]

Kinda new to ms access so please be patient. Purchased a software package to do job costing, the major selling point was it was “written” in access and we could do some custom forms, reports etc. After linking to the existing tables we made some rather nice queries and reports, so far great. Now we are trying to make a form to enter information to the existing tables, we can make the form work but not in a user friendly way. Here is the problem, there are 2 fields record_id and job_no that are not autonumber fields but are set to “required and no duplicates” what I would like to do is upon opening the form is

1) all fields blank except record_id would be one number more than the last record in the table

2) job_no field would be one number more than the last record in the table

3) customer name field would have our most used customer name and address in the blanks and the customer name field would have a drop down option with the rest of the customers

4) a button of the form to enter the info into the table and reset the form

maybe this is not possible but any help would be appreciated, I have bought several books on access and searched numerous forums, that made the quires and reports possible but the forms seem to be much more of a challenge. Many thanks for the help

 

[TSIsolutions]

Hi PaulBol,

Maybe I can help with certain aspects of your question. First, you may need to do a little bit of programming to get this done. The record_id should've been automnumber to begin with... I think. But we'll work with what we have.
1) and 2) In the BeforeInsert Event of you form, you can use the aggregate function DMax to find the values that you need. You would have to enter the following like such:

Public Sub Form_BeforeInsert(Cancel as Integer)
Dim MaxRecordID as long
Dim MaxJobNo as long

MaxRecordID = Nz(DMax("record_id", "[enter table name]"),1)

MaxJobNo = Nz(DMax("job_no", "[enter the table name]"),1)

Me.record_ID = MaxRecordID
Me.job_no = MaxJobNo

End Sub

The Nz Function is a preventive measure in case there are no record_id or job_no.

3) This point is a little more complicated because the system needs to count the amount of time each customer is used and take the one with the highest number. Personally, it doesn't seem like the best idea only because you want the field to be blank and selected by the user in order to avoid mistakes. You can use a ComboBox and have the user simply type the first letters of the company name and once selected the address would appear. If you still want to find the customer with the highest usage let me know.

4) Your last point is actually very easy. Access automatically saves the information of a Bound Form when you move to a new record. So by clicking on the new record button in your navigation buttons or create a button and enter the following in the onClick Event:

Docmd.GotoRecord,,acNewRec

Hope this helps. Let me know,
Lawrence

 

[earthandfire]

Just one point with using DMax to get the last (and therefore work out the next) number. It could be problematic in a multi-user environment. A safer method is to have the current last number in a field in a seperate table. Attempt to open this table exclusively in a loop (in case another user has it open). Get the current value from the relevant field, increment it and write it back and then close the table. You now have the next number. There are numerous examples of the code for this on several of the Access forums here.

Hope this helps.