I'm making a new database and I want to assign Item Codes to my inventory. For example:
ItemCode (Primary Key) | Item | ItemDescription
I want to generate an automated item code containing the first letter of the Item (as prefix) followed by numbers. Example:
ItemCode: P001
Item: Paper
ItemDesciption: Ordinary white paper
Hi All,
I searched the forum before posting and couldn't find my problem, or how to adapt similar problems.
I have created a Database for call logging.
I have 2 types of call Change and Problem. I have 2 seperate tables for these. Each have an Auto Number as a primary key. One Prefixed with a 'C' and one with a 'P'. This was all fine, problem now being i have created a new table for Time Management.
I am trying to link the the time to a call, i can link the 2 primary keys to the 'reference' field in the time management, the problem being there are now 2, 000001, i can manually re-attach the P or C, but i want to write a report to see who's logging the most calls, to do this i wanted to use the time to look accross to Problem or Change and show the User ID, but when i query C000001, it show's no matchs as all thats stored in the table is 000001.
Any help or advice would be much appreciated.
Thanks,
Ian
I found instructions that said in order to prefix an autonumber i.e. AAA000 you would have to write "AAA"000 in the format section under the general tab in the table design view of the field you are interested in.
I did this however even though the number now appears as AAA000, AAA001 etc in the table it does appear this way in the form - it just appears as 0,1 etc....
My actual prefix is 2006/1010/ i.e. 2006/1010/001, 2006/1010/002 and so forth.
Anyone have an idea of why not?
So I have a table with a bunch of data, InventoryID is the autonumber field which just increments normally, what I want to do is stick 'S000' in front of the autonumber, and for this to be the default prefix for any new items in my database - how would I go about this? I'm guessing some kind of append or update query would do it, but I'm no access whiz, help appreciated folks.........
View 2 Replies View RelatedOk...I've looked at old threads and now I'm more confused than before.
I'm trying to find a way to make my field "PCRTrackingNumber", work like this: 05-0001...05-0002...05-0003...and so on. The 05 represents the year. I currently have the field setup as autonumber, set to Long Integer and increment. Even if it looked like 05-1...05-2...05-3...etc. that would be fine. Basically just looking for a way to add "05-" to the prefix.
I was trying to think of a way to combine a field to the autonumber field and use that instead...but need some help.
Thanks, Jeff
I have a module which generates an autonumber based on a table content (Tbl:Numbervalues) and a prefix (prefix held in Tbl: TextValues)
the module itself works fine and after every getprefix & getnextnumber it updates the number within numbervalues table to the next unique figure.
However what i want it to do is within my database i want to loop through all the records and update a column based on the getprefix() & getnextnumber()
so at the moment lets say column A has values of SHA000001 (in all 100 records) i want it to loop through those 100 records and update to be SHA(from the prefix table) and 000001 all the way to 000100.
Code:
Public Function GetPrefix() As String
On Error GoTo GetPrefix_ErrorHandler
GetPrefix = DLookup("Value", "TextValues", "Description = ""InvoicePrefix""")
If Len(GetPrefix) <> 2 Then
msgbox "The Invoice Prefix Value in the Text Values Table is not 2 characters long!", vbCritical, "Critical Warning"
End If
[Code] ....
Is there a way to prevent Access from the absurd notion it needs to concatenate 'DBO_' as prefix to tables linked from SQL Server db? :mad:
Thanks.
I want my JobCardNumber field in table T-JobCards to add automatically month number.
e.g. For January
1-00001
1-00002
1-00003
For February
2-00001
2-00002
2-00003
OR
Jan14-0001
Jan14-0002
I know the method of setting format in tbl as 000 etc.. but this is one specific.
My database has three tables with many columns. The three tables are identical in the names of their columns.
I want to copy all columns from all three tables together into one single table, giving the respective columns prefix table1-, table2-, table3- since the columns would otherwise be indistinguishable.
I already tried to search the board for "table columns add prefix".
I use Access 2010
I managed to copy all columns together into one table through design-view, but cannot figure out the "add prefix" step.
I have a table which will contain project information, and want the ID field, which auto assigns a unique number to each record, to be formatted like this P12-001, where 12 = the last two digits of the year the record was created. I know that if I type into the format field "P"00"-"000 i get the ID number in the format I want, but cannot figure how to get the first two zeros, to be the date format.
Further, I would like the ID numbers to restart from 1 each year, so this year this first project, P12-001 and may run through to P12-063, then the first project I enter next year to start P13-001.
I have a table named tblProjectNumber that has a autonumber field. In design view under the format property I have "NC07-"00000, this returns the a new project number with the text in front of it. What I would really want is for the text the change dynamically with the year. So an example would be NC07-00000 for project numbers created this year but I will manually have to change it for next year and the all of the previous project numbers will change accordingly. I have tried "DateSerial(Year(Date())-"00000 , Date("yy") and I can't get it tto work.Any help would be great.
Tim
In trying to respond to another thread, I have run into something that is confounding me (or maybe I'm just getting dense).
We have a subform. One field has an event on DblClick to launch a search form. When the user identifies the target, he/she clicks a button on the subform. This pushes the appropriate value into a field on the original subform using VBA code and closes the search form. This all works fine.
The behaviour that is driving me bugging is when the user clicks on a new record (i.e. new line) on the subform, we would like to automatically generate the next record (E.g. when you type in a field of a record with autonumber in datasheet mode, Access automatically generates the next record). Currently this doesn't happen - Access generates the PK for the record being modified, but doesn't generate the view of the next record.
What really confuses me is that I have created similar looking example in which this works just fine. I can't figure out which of the differences between the two samples is causing this behaving.
Also, typing information into the field on the subform does cause the next record to be generated. It is just doing this via code that works in one case but not another.
I have narrowed it down to the actual subform. Even as a standalone form the form exhibits the same behaviour.
For reference, the original thread is
http://www.access-programmers.co.uk/forums/showthread.php?t=99457
Any suggestions?
-grommit
We sometimes make an error in entering a stock code and have to change the code.
Our main table is = Investments01_tbl
Other tables that use the same stock codes from the above table are
= Alerts_Stocks
= Investments01_tbl_SubForm
The table = Investments01_tbl_SubForm = can contain the same stock code many times.How can we create a query that can be used to enable us to change the stock codes in the 3 tables at the same time?
I have several tables which have an indexed, no dup field. When inputting a entry that is not in the referenced table, how can I be taken directly to the input form for that field.
View 1 Replies View RelatedIm trying to create a database to track who worked on what item and on what date, with four tasks required to complete the item.To give an example:I have a factory that builds Lego models, for each model there are four steps:
1)Unbox Parts
2)Sort Parts
3)Build
4)Check build
Any employee can work on any task, and multiple tasks can be done by the same employee.Having a completed item table with a field for each task, and a date for each task, with one to many relationships from employee id to each task type. This failed as I needed many to many, so I made a junction table to link them, and this is where I get a little muddled.
Do I need a table of task types, then a table tracking each task to feed into the item table, or is there a simple solution I'm overlooking?
Im trying to make a basic customer database and pos system. The part iam having trouble with is, I have two tables as follows:
Inventory: orders(invoices):
productId ProductId
amount (in stock) amount(sold)
pricePerUnit CashTendered
I want the amount(in stock) sold in each order to be deducted from the total in the amount(sold) automatically.
I have a form that simply lists the items:
DEPARTMENT_NBR and DEPARTMENT_NAME
In the table: DEPARTMENT_TBL
When I edit the field DEPARTMENT_NBR and it is in error (must be between 01 and 99) I want to put out a message in a MSGBOX and SetFocus back on the DEPARTMENT_NBR.
I coded the MSGBOX with vbokonly and then DEPARTMENT_NBR.SetFocus, but after the message displays and enter is hit for the ok the cursor jumps to the DEPARTMENT_NAME on the current line. Is there a way to get the SetFocus to work properly on repeating items like this? I can never seem to get them to perform the same as they do on non-repeating items.
Thanks for your help.
HERE'S THE EXACT CODE:
If IsNumeric(DEPARTMENT_NBR) = False Then
If DEPARTMENT_NBR <> "00" Then
MsgBox "DEPARTMENT NUMBER must between 01 and 99.", vbOKOnly
DEPARTMENT_NBR.SetFocus
GoTo DEPARTMENT_NBR_EXIT (exits the paragraph skipping other checks)
End If
End If
Also, is there a way to look at a specific item in a list like that? IE. I want to reference the 3rd row's DEPARTMENT_NBR. Thanks.
I have a MAIN table with autonumbering for the project number. In a table related to the MAIN table I have a project number that I want to be updated when the MAIN table has a new project entered. Can I keep these in sinc automatically?
View 8 Replies View RelatedHi,
I need to create a table with 2 sets of different information both referring to job types. the problem is that i need the autonumber which created the job number to scale together. so both tables have a primary key of "job number" and i want the autonumber to only ever use 1 number in both tables.
e.g.
job type 1 - autonumbers - 1,2,3,4,6,7,9,10
job type 2 - autonumbers - 5,8,11,12
is there anyway of achieving this?
Any help greatly appreciated
OK, My main table has an autonumber field in it to make records unique.
At the moment, the number 5-34 are all in use on active records. But when I create a new record either in the front end form, or the back end directly in the table, it attempts to start the record off with an already existing number.
No idea why this is doing this at all.
Is it possible to reset the autonumber to say 50 so all new records start at 50, then 51 etc.
I really dont want to lose the numbers already assigned, as there are other tables that rely on that autonumber as the clients ID to record all the other data.
Its probably something very simple, but I cant carry on with my data entry at the moment.
I have three tables.
EAch has a field called ID that is an Autonumber that is the primary key.
When I enter data via a form each record has a different ID across the three tables.
Does this mean they are not linking up correctly?
I have attached my databaset if that helps.
Thanks.
I made a simple access project and I want to make a count for my visits, this counter MUST starts from one each morning "every new day", I use autonumber field to give each visit its unique code, I'm ok with that, but i need additional counter that resets each new day,, i watched some videos for making the autonumber starts from different number like 1000 for example using Append query but i didn't figure out how to use this method for resetting counter everyday.
View 6 Replies View RelatedA slight flaw in the design requires this change. What is the best way to do it?
View 8 Replies View RelatedMy database exports data to one of our customers who then inputs the data into their system.
One field i have is "RO Number" which i have as an autonumber which works for the purposes of my part of the system.
However they have it set as "RO9999", (Their system has been set up for years, we are a new "branch") but they aren't in access.
They want the RO number to be a running serial from RO30000, RO30001 etc
The data in entered on a form.
Why one would want a unique index on an ID key ? It can't be effectively used in joins and is never used in a where clause. So why have one ?
View 14 Replies View RelatedI am creating a database for cases. I want to set autonumber into sequence, Let say if there are data numbering 1,2,3,4,5. If I delete no.2, The data will rearrange in oreder from 1,2,3,4. While If I add a new value it would be the no.5.
Instead of data value autonumber 1,2,3,4,5 as I deleted no.2 and add new records. The data has become 1,3,4,5,6.