Questions regarding how to autofill and autonumber a form from a table.

[794613]

Hello, I'm very new to access (about a week I've been working on this database) and am trying to make a database to track jobs for a company I work for. I've got the layout side of access laid out fairly well, but I'm having trouble with the programming side of Access. I've got one main table that the information will be pulling from called tblCustomers. This contains the information that I want to display on the form I've attached. Basically I'd like to use a drop down menu under "customer name" on the form. When I select a particular company I need the other related information such as "Customer ID, Contact, Street, City, State, and Zip Code" to automatically fill from the information contained in tblCustomers. This is what I'd like it to do, however I'm somewhat lost on how to go about getting that working. I've read that the dlookup command should do what I need, but again, I'm not sure. This is why I've come to theScripts, please help. I appreciate any information you can give, or point me toward. One other item I'd like to get to work after getting the autofill to work is that I'd like the Job # field to automatically fill out with a unique number within a range of positive numbers, say 5 decimal places 0 - 99999? Thank you again for any help you can provide.

Picture of the Form.

- James

[794613]

Alright, slowly but surely I'm making some progress. I was able to get the Customer ID to autofill, although it has started to fill the Contact information with the same info as the Customer ID. Eitherway, how would I go about setting another command in the same event? Is there a divider I can use, or simply another line to type the next part of the code?

Thank you for any help you can provide,
- Jim

[nico5038]

First you need to think about the construction of your tables and their relations.
Assume an Orderform, this will be based on a tblOrder, but also have a relation to a Customer.
To get the Customer details you can easily add a subform that's linked to the CustomerID of the tblOrder and than Access will synchronize the Customerdata.

When you make the CustomerID on the frmOrder a combobox to lookup Customers from your customer table, the data of the subform will also change after the selection of another Customer.

Perhaps it's worthwhile to look into the Northwind sample database that comes with Access and/or can be downloaded from the MS site.

Nic;o)

[794613]

Thanks for your help Nic, I was able to get the sub form to work (wasn't aware of a subform option). Thank you very much for your help. Now I need to figure out how to do an autonumber system that has a set number of decimal places and counts in increments and will not lose count after a database compact and repair. The number needs to be 5-6 digits in length, non-repeating, etc. It also needs to start with 0's in blank digit areas, ex. instead of just the number 1, it needs to be 00001. These will become job numbers. If you could point me in the right direction in that regard, I'd appreciate it. Thank you again.

Here's the updated form.

[794613]

Also, is there a way to make the autonumber field start from a certain value? So instead of autonumbering starting from 1, it could start from say, 100, or 151? Thanks.

- Jim

[nico5038]

I've created in the past a sample database to show a controlled number insert:
http://www.geocities.com/nico5038/xC...Insert2000.zip

Nic;o)

[794613]

Nic,

I appreciate all the help you've given as it has helped me quite a bit. I come again to ask your help with what I hope to be my final question for some time. The following is what I'm trying to accomplish:

1.) I want to create a table that will have 6 fields, and 5 subforms. The first field will be a date field, and the next 5 will be Job-1 through Job-5. I'd like Job-1 through 5 fields to be combo boxes that pull the job number from the job table. Subforms 1 through 5 will each be located under each Job drop down combo box. The idea is that by selecting a job number in job number 1, the subform will display the necessary information that I want from the Job table. I can clarify if necessary. Eitherway, the idea of this is that I will create a form for the Daily table (Daily Worksheet) and I'll be able to select our current jobs by job number. This way the employees working in the shop have a daily list of what jobs they are working on and such. I don't know if what I need to do is even possible. I'm sure it's something basic I'm missing. I've setup the combo boxes on the Daily table to pull from the job menu. And I've added a subform with the data from the Job Tracker. But when selecting the job number, the subform isn't automatically updated. I'd be willing to upload the database if someone could take a look at my work and see what needs to be done.

Thank you very much,
- James

[nico5038]

You'll need to manually link each subform with the appropriate combobox.
Just single-click the subform and open the Data tab of the properties.
place the comboname in the Main linkage field and the corresponding key in the subform in the Child linkage field.

Nic;o)

[794613]

Nic,

Thank you for all your help. I went ahead and created my Daily form from the Daily table. The Daily table has the following fields:

date
jobnumber1
jobnumber2
jobnumber3
jobnumber4
jobnumber5

jobnumber1 is a combo box that pulls info from my Jobs table. That part of the form is working well. It has a lookup column that I set up that searches by Job Number and Customer Name. The issue I have is even though (as far as I know) the form and subform are linked correctly, the data is not automatically filled in the job subform after selecting a job number.

Thank you for your help,
- Jim

[nico5038]

Make sure that the bound column (normally col 1) is returning the proper value to link the subform.
Manually linking works always, at least with the proper values (also check the datatype of both fields!)

Nic;o)

[794613]

Hello Nic,

Thank you again for your help. I hate to keep using this same original post to ask other questions, but no one seems to respond to my other threads. Any how, I've gotten my drop down daily worksheet to work thanks to you. It was in fact the bound column that was giving me issues. The new issue I have should be the last for a bit. This is what I've got:

The Job Tracking form (Jobs) has a "jobnumber" field that I'd like to use an autonumber (unique) in incremental mode. That by itself isn't an issue. However if I switch that job number to an autonumber field, I cannot properly link my Daily Worksheet form (Daily) from each look up column (combo box). When I change the data type to number for example, it just doesn't seem to work. Can you give me an overview of how to properly setup the following:

- Autonumber field for Job Number in the Jobs Table/Form.
- 3 separate combo boxes pulling info into subforms.
- What data type needs to be selected for those 3 combo boxes?

Thank you Nic, if you need more information, just let me know.

- Jim

[nico5038]

Changing a datatype "down the road" isn't easy.
Not only the tablefield needs to be changed, but also the fields used on the forms.

In general a "rebuild" of forms and reports is the best solution to make sure nothing is forgotten. Guess you found out why I spend lots of time with validating my table design... :-)

In general I wouldn't use an autonumber for a "visible" Jobnumber. For me the autonumber is normally "hidden" for the user as it won't allow much flexibility in setting ranges....

Nic;o)

[794613]

Nic;o),

From my research with access I understand the issue with using a visible autonumber field, but I cannot seem to find a better work around. What would you do to create an incremental, "auto number", with formatting options for a job number?

Thanks,
- James

[794613]

Hello, I've got an update to my current issue. I used the DMax function to add a number in sequence to the jobnumber. That is working, here is the code I am using.

jobnumber = DMax("JobNumber", "tblJobs") + 1

And so far this is working. The issue I have deals with where the best location is to insert the code in. I'd like the number to be updated before any data is entered into the new record. Or even better, a fail safe where it requires user input in the form of a button plus a warning that clicking this button will make a change to the job number. I'm trying to keep sequential numbers to keep track of the job number, but at the same time make it so it's not something that will be changed accidentally.

- Jim

[nico5038]

I generally use the created number to INSERT a new row and then open an INSERT form (that's effectively an update form) using the created key for the stCriteria filter.
The only difference with an ordinary Insert form is that the form's Cancel button will delete the record...

Nic;o)