New Database Help (1 Viewer)

[saip15]

How can I reset my ID autonumbers? I have been testing out how the data entry will work and each time I enter a record it assigns a value. I know this is what it's supposed to do but can I reset them after I delete the record? Thanks!

I'm trying to get the form functional.
How would you recommend adding each reviewers name? Remember, I have 6 people that will reviewing a document and it would be nice to have a drop down list for each 6 positions? I was thinking about adding these in the relationships somehow but I'm not sure where the best place would be? Or can I just add combo boxes with the specific names I want and relate those to the drawing number, so when that drawing number gets pulled up on a query those names from the combo boxes would get pulled up to? Or do the names have to be in a related table?

If you don't mind could you please make a quick form using the wizard with all the info. I'm having a hard time choosing the right settings to make it work? I know it's just going to take some time to get it the way I want. Thanks a bunch!

Here's what I have so far.

 

[jzwp22]

You can only reset the autonumbers for tables where you have deleted all of the records. You would use the compact and repair utility within Access.

Since you have multiple reviewers, you need a related table. I assume that only a few people of the HWTeam can be reviewers. To handle this, you can add a yes/no field to your HWTeam and mark those people who are reviewers, then you can use a query with the yes/no as a filter to populate your combo box.

I went ahead and did a mainform/subform/subsubform for you in the attached DB. I also added the yes/no and used it to filter a combo box for the reviewers.

 

[saip15]

I can't thank you enough jzwp22! Did you use the wizard or design view?

I need to add atleast 8 different text boxes or places for different revisions. Like in my previous post I had the RevLevel1-8. Where can I add those in?

I split my HWName into a HWFirst & HWLast. How do I make the name appear as one from those two tables? Let's say in a combo box?




Hi chacha,

You might get more assistance if you start a new thread.

 

[chacha]

Hi!
i am a new user of this forum and i have a some problem regarding some assignement given my boss, basiscally i am working in one of food chain restaurant and my boss asked me to prepare the file of costing which should have a link of receipes and when we put the sold item of menu and it autimatically show the consumpiton of each raw material.
Do you have any idea i personally appreciate your thinking.i am very thankfull for you.

Rgds

chacha

 

[pbaldy]

Chacha, again, please don't post the same question numerous times:

http://www.access-programmers.co.uk/forums/showthread.php?t=174989

 

[jzwp22]

You're welcome

Did you use the wizard or design view?

I used the wizard to create the basic form and then edited it in design view to add the combo boxes.

I need to add atleast 8 different text boxes or places for different revisions. Like in my previous post I had the RevLevel1-8. Where can I add those in?

Each revision should be a separate record in DrawingRevisions; you do not need any other textboxes.

I split my HWName into a HWFirst & HWLast. How do I make the name appear as one from those two tables? Let's say in a combo box?

Starting with the query qryReviewers (using SQL view) I had in the database I posted yesterday

SELECT HWTeam.HWID, HWTeam.HWName, HWTeam.Email
FROM HWTeam
WHERE (((HWTeam.Reviewer)=-1));

The new query will look like this:

SELECT HWTeam.HWID, Trim(HWTeam.HWFirst) & " " & Trim(HWTeam.HWLast), HWTeam.Email
FROM HWTeam
WHERE (((HWTeam.Reviewer)=-1));

The Trim() functions remove any blank spaces at the beginning or end of the fields. I added a space between the first and last names using the space in between two quotes: " " The ampersands (&) join the two fields and the space into one field.

 

[saip15]

Thanks so much for all your help guys!

I have a subform of revisions and I already have the PK set to autonumber. I have a field inside the table which is a sequence number. I would like this field to autonumber when a new revision is added. But as you all know you can't have two autonumber fields. Is there anyway to make two autonumber fields? Can I make a number type into a "autonumber" somehow? I just want the numbers to increment with the number of revisions. Thanks again!

 

[jzwp22]

You will need to add 1 to the value returned by the DMax() function to create your own incrementing field.

 

[saip15]

Do you think I could get some more detail on how to do that? Sorry!

 

[jzwp22]

I take it you want to increment the revision level (Revlevel) field in the DrawingRevisions table. First, you will have to change the data type of the RevLevel field to a long integer. In the before insert event of the subform, you want to add the following code.

Me.RevLevel = DMax("RevLevel", "DrawingRevisions", "fkDrawingID=" & Forms!frmDrawings!pkDrawingID) + 1

I've amended the previous database I posted with the code indicated above.

 

[saip15]

I just want one of the fields to act like an autonumber field. Is this the easiest way to do that. I don't need these values to be stored or anything.

So how can I increment a field without using autonumber, since there can't be two autonumber fields in a table.

 

[jzwp22]

Yes, it is the easiest way to do it, and it needs to be stored so Access knows what previous number was. I assume that you have a business practice/procedure that covers how & when the revision level is incremented. I would just design Access to mimic the business practice.

 

[saip15]

My database has change a little from the original. I have attached the subform by itself. I want the sequence number to auto increment when a new revision is added. Thanks so much for your time.

 

[jzwp22]

The code will not fire properly with just the subform by itself because the drawingID must autopopulate from the mainform prior to the code firing or the code needs to reference the drawingID on the main form. Also, you are using the field name seq#. It is generally not recommended to have spaces or special characters in your table and field names. I would suggest seqno.

 

[saip15]

Thanks jzwp22! Sorry, do you want me throw the other table in there?

So I have my form set up and working great! My next step is to be able to search for specific data that is in the database. What you guys recommend as a good starting point? I'm researching search forms now. Thanks so much for all your help!

 

[jzwp22]

You should be able to adapt the code I had in the earlier database I posted to your current form/subform. You would just need to use the new table and field names.

With respect to searches, most people end up using some sort of search form which is typically called Query By Forms (QBF). Here are some links to get you started:

From Allen Browne
From Microsoft
From Roger's Access Library

 

[saip15]

Thanks so much for all your help guys!

I wanted to get some opinions on security and permissions. This database needs user-level security and log-in. Is the User-Level Security Wizard the simpliest and most effect way to set up security for this database? I have been playing around with and seems pretty simple to set permissions and such. This database will be placed on a network drive so everyone can use the database. I know splitting is always a good idea but I'm not sure if it's right for this case. If I do split the database will I place the FE and BE into the same folder?

I have a couple more questions but I thought I would get some input before planning the next step.

Thanks

 

[jzwp22]

If it is a multiuser application, you must split it otherwise you will corrupt the database. The backend (tables only) will be put on the server while the front end (everything other than the tables) will be put on each users' computer's harddrive.

I used the security wizard along with some controls on my forms if I didn't want users to do certain things.

 

[saip15]

Here is my problem, as of right now my supervisor said to keep it simple and account for only one user using the database at a time. I don't want to deploy FE's to each computer because there are only a select few people that will use the database and even then I don't know how much they will use it. Can't I just convert the .mdb to and .mde for the user to run? Could I still split and use the FE on the network drive?

I had a question about splitting. Would this allow multiple users to use the database at once? If two people are working on data entry how does the db know how to store the data without overwriting, I know it is a continous connection from FE to BE but how does it work with two users? Thanks

 

[pbaldy]

In one sentence you say you only need to account for one user, in the next you ask about multiple users. Do it correctly now; split it, with front ends on each user's PC:

http://www.granite.ab.ca/access/splitapp/index.htm

http://www.granite.ab.ca/access/autofe.htm