I have a query on a timer that basically looks at a linked table every minute and tells me whose in the database. I want to number the rows in a column so I can reference this to a form that has 7 spaces. The number of 'users' could vary from 0 - 23, although this will soon be limited to 7 (hence the 7 spaces) to aid performance and speed. I am then going to reference the number to the unbound space on the form, iif(isnull()) 'ing the blank cells so it isnt an error.
I thought of an append query, but the sequence will always start at the next number, not from 1 each time - or as far as im aware it will - so I cant reference the cell then.
We have a part numbering system that is currently like this, 11Y22 where 22 increments by one until it gets to 99 where it then goes back to 0, once it goes to 0 the letter increments to Z in this case, now once the last three characters get to Z99, then the 11 gets incremented eg, 11Z99 will become 12A00. Hope that is clear :)
My question is, can this system be implemented into the auto numbering in Access?:confused:
If I have taken a program to creat a new program......I have cleared all the tables and what not. When I enter my first record in the auto format number starts at 963....... How do I get this starting back at zero?
Not sure as to whether or not this query shoulsd go here or else where? But here is the problem. I have been asked to repair a database that has lost the autonumbering facility. Apparently this was caused by somebody leaving the database open when the network crashed.
Each team can access the database via forms but now when the team responsible for registring new pieces of work tries to enter a new piece of work the autonumber reverts to one already within the database.
I was told that the only fix to this problem was the DMax function which I am unfortunatley not familar with. Can anyboby explain how to go abouit this and where it should be used.
*I know that this may not be the best way of resolving the problem but this is not the actual problem but my way to simplify of explaining the "problem".
I have a Table with 3 fields. Field 1 - Auto Number Field 2 - First Name Field 3 - Last Name
Initially, the Table was sorted via First Name, but now, there is a change in requirement to sort by Last Name.
Next requirement is to number (Auto Number) according to the Last Name.
Current Sorted via First Name Auto Number - First Name - Last Name 1 - Ben - Young 2 - Chuck - Norris 3 - Dennis - Johnson 4 - Ernest - Anderson
If sorted via Last Name 4 - Ernest - Anderson 3 - Dennis - Johnson 2 - Chuck - Norris 1 - Ben - Young
Desired result to Sorted via Last Name & (Auto Number) via sort. Auto Number - First Name - Last Name 1 - Ernest - Anderson 2 - Dennis - Johnson 3 - Chuck - Norris 4 - Ben - Young
Is there a way to do renumber / resort the auto number this?
Although there are few records in my database, the auto-numbering started at 45. So I deleted all the records so I could start from zero but it still happens. I checked all the properties of that field both in the form and the table itself and I can't find the problem. Any ideas would be most appreciated. :confused:
I was entering records using a form and all was well until suddenly the autonumbering went from 7 to 14! I went into the table and deleted #14 and tried it again - same thing happened.
When they select Event 1 in the form I would like it to generate a unique Identifier within the range that Event 1's fall into. It would be an incremental increase for each Event 1 enterered. The same for the other 9 events.
The Event #'s are a quick and dirty way of knowing what type of event has taken place without reading any of the description. It is a requirement for this and I have no way around the Event #.
Is there a way to reset the auto numbering? I import new data then archive the old data. As I do this weekly my auto numbers are getting high and my users do not care for this aspect
I have 5 copies of a database that 5 users enter data into (including Pre, Post and Follow-Up information). These users send me their database quarterly and I am suppose to merge the data into one database from which to run reports, etc.
The problem comes in with the auto-numbering. Each "Pre" table has an ID that is auto-numbered. This auto-number is also stored when a corresponding "Post" (or follow-up form) is entered so that we can be sure the pre-, post- and follow-up are all linked to the same individual. When I merge the data, they are reassigned ID numbers in the Pre (while the Post still has the original Pre ID number) so now I've lost the link as to one individual's pre, post and follow-up form.
I realize now that the database shouldn't have been built this way, but is there any way to work around this. I've even tried exporting all of the rows for each of the 5 users into Excel and then importing them into Access in a new table (just so I could have the ease of running queries) but I seem to be getting errors doing that as well.
I have 5 copies of a database that 5 users enter data into (including Pre, Post and Follow-Up information). These users send me their database quarterly and I am suppose to merge the data into one database from which to run reports, etc.
The problem comes in with the auto-numbering. Each "Pre" table has an ID that is auto-numbered. This auto-number is also stored when a corresponding "Post" (or follow-up form) is entered so that we can be sure the pre-, post- and follow-up are all linked to the same individual. When I merge the data, they are reassigned ID numbers in the Pre (while the Post still has the original Pre ID number) so now I've lost the link as to one individual's pre, post and follow-up form.
I realize now that the database shouldn't have been built this way, but is there any way to work around this. I've even tried exporting all of the rows for each of the 5 users into Excel and then importing them into Access in a new table (just so I could have the ease of running queries) but I seem to be getting errors doing that as well.
I could not use 'Auto Numbering' for each line, as I need out some lines accumulated for one invoice number 'ICP001'.
My questions:
1. What can I do to let me Automatic generate invoice number which increment from last number? 2. What can I do to let me easy to put those 'invoice number' into my Data Entry table's invoice number field? (p/s: Append Query? Update Query?) 3. What if I need sorting (example to group ONE customer and ONE Particular) for my invoicing number? What I meant is ... let say there is 30 lines customer CUC with particular Part B in a same month, and I wanna get them into same invoice number.
I am using an access program with a form with detail section. I would like to create auto sr.No when i am creating a new quotation. Using Autonumber didn't work because it start numbering from last quotation.
The situation is that I have a table named patient and a form named [patient1] where it holds a textboxs named "MFC_Seq_no" and another to hold [date], now, I manged to do a manual auto numbering for "MFC_Seq_no" using the following VB :
Private Sub Form_Current()
If Me.NewRecord Then On Error Resume Next Me!MFC_Seq_no.DefaultValue = Nz(DMax("[MFC_Seq_no]", "Patient"), 0) + 1 End If
Whenever user create new record, the system will automatically store the next number in the new record.
Now, I've be requested to code that system to start again from 1 as value for "MFC_Seq_no" whenever there are changes in date field (i.e. next day in the date field)
Is this doable and how?
Q2. how to duplicate an entire record with a subform, from another table, in it?
I have created quite a substantial and effective database for a small gliding club with all the major data tables being linked. I need to be able to re-index tables periodically so that running totals, which are needed to calculate statement balances, works correctly - all well and good. The only problem is that the process of re-indexing requires the data in the main table to be stored temporarily and the original data deleted. When the temporary data is appended to the main table the auto-numbering just keeps clocking up.how to reset the auto-numbering in a local table.
I have the auto ID number set up set up on my Access database which gives me membership numbers. How do I have a supplementary auto numbering column to give me invoice numbers. You used to be able to do this automatically up until a few years ago. Now I have to enter them manually.
Hi i was wondering, i have two forms in my database.
On one of the forms when doing a combo box wizard i can select the option to "find a record on my form based on the record i selected in my combo box" but on the other form in the same database which i have it does not give me this option.... Does anyone know why this is so... Is it a relationship issue with all the tables???
If it is a relationship issue is there another way around this so i can do this option but without changing the relationships... Any help would be greatly appriciated...
I using access 2010 and I am trying to code (VBA) one of my combo box to auto select if another combo box consist of multiple selections. So for example I have a multi select combo box that has a list of departments and based on what the user selects I want the other combo box to automatically select itself if one or more of the department in the first combo box is selected:
I want to distribute certain materials (found in a query) to several warehouse locations in a sequential order.
So the first found material needs to be put in warehouse location 001, the second found material needs to be put in warehouse location 002, etc.
How can I number the lines in a query?
I don’t want to run a create-table-query with an auto number field because the warehouse locations are limited and every time the query runs the numbering has to start at 001.
I would like to create an expression that would number each item in sequential order in a query . I cannot use autonumber on the table because I am pulling it from SQL and do not have clearance to change the actual tables. I have been searching through this forum for the last 2 hours and have not come across anything similar to this, so I hope I'm not covering a topic that has already been covered.
