Automaticly Generating Prim Key
Apr 18, 2008i need to automaticly Generate a Primary key for RMA Numbers so i want Previous forms number + 1 and i want to start with Number 10832.
View Replies
ADVERTISEMENT
If Prim. Key Exist - Update
Jun 12, 2006Hello all. I'm trying to finish up a database for our farm and am running into issues as I'm pretty new to Access. I know VB6 but not a lot of VBA. We spray chemicals, and need to keep track of the acerage we spray for the date.
So here's my problem:
When I go to write my records, I need to search a table for a date. If the date exists already, then I need to update the total acreage related to the date. So first, how do I search the table and keep track of if the date was or wasnt found, and if found, calculate total acreage from value pulled from table, and the new acreage value, then write that back to the table.
Would really appreciate the help. Thanks.
Default Value Disappears For Prim Key Field
Mar 16, 2006Hello,
MS Access 2000 sp3
When adding a new record to an order file, I have a default value on the ordnum field. It also has a format = "9999-999". The default value is "0603-".
When the user clicks on the new rec nav button, the form has the correct default in the ordnum field "0603-". However, when the user trys to key in the remaining 3 digit sequence number (.i.e. result 0603-123), the default value disappears as soon as the use startes to type the first character. The table source is a query that links the order table to the customer table via cust num. When i use only the order table as the form source, this works ok, no issues but i see this issue when i am using a query joining two tables.
Does anyone have any ideas why or how I can correct this?
Thanks!!
zip file attached with test database
Schedule Next Appointment Automaticly
Dec 29, 2005Good Day everyone,
I am looking at setting up appointments and having Access automaticly schedule the next appointment in either 6 months or a year depending on the Vender. I've search the forum and can't find anything.
For Example if their last appointment was 12-1-05 I would like access to automaticly schedule the next appoint 12-1-06.
Any ideas?
Thanks,
How To Import Dbf Files Automaticly?
Apr 9, 2008Hello all
this is my first post. I've tried to get my info without any luck so i ask my question here.... hope it's ok.
I want to import some dbase files that comes from an accounting software and ask Access some queries. The queries part is perfect. My only problem is how can i ask (in a general menu of my Access application) access to go and get those dBase files?
I want the user to be able to have a button asking him : " Do you want to import new data?. And then it will update Access. I don't need to append the data just overwrite the old data. I'm not doing any manipulation of data on the files, just asking questions. (Hope i make sense...)
They are always on the same directory on the server.
Thank you for the help!
How Do I Save Automaticly A Repot?
Jan 13, 2008Hello,
I made reports in Access.
My question is how can I send reports AUTOMATICLY via mail (I know how to create the button to do that, what I am looking for something that does it once a week without me doing anything about it).
Or save it to a know folder AUTOMATICLY???
Generating Numbers
May 5, 2005hi,
I have a table that has results from races, eg.
Race No, Racer ID, Time taken.
i want to have a position field that will say which position the runner finished in. This is complecated as there are many different races.
At the moment the best thing ive been able to do is set up a query with a parameter on the race no. then the time taken is put in order and i have to manually put in the positions.
Manual is bad!
does any one have any idea about how i could make this automatic.
Thanx,
MAtt
Generating A Unique ID
Nov 2, 2004So I've read a good 100 postings or so so far on the subject of how generating a unique identifier through any other means than an autonumber can be dangerous. But I can't resist the opportunity to simply ask if there is a way to make this possible, if only through a calculated field...
In my single user database, I have an employment table with an autonumber [EmploymentID] field as its primary key. The employment table exists in a one-to-many relationship with it's child, the income table where each form of income is recorded as it is recieved so that one form of employment (or employer) can produce many unique forms of income (like weekly paycheques).
I would like to be able to generate an identifier for the income table that is a combination of the parent [EmploymentID] field and an increasing number to produce something that looks like this:
1006789-001 -|
1006789-002 -|--> Same EmploymentID
1006789-003 -|
1006790-001 ----> New EmploymentID with a reset numeric
Any ideas?
~Chad
Generating Primary Key
Jan 17, 2007I have three fields, One called Key, one called IS and one called BC. I want the table to automatically copy the number from the IS field to the Key field to be used as the primary key. However, this field does not always have a value, when this occurs I want it to take the value from the BC field. Is this possible?
View 3 Replies View RelatedGenerating Records
Nov 28, 2005Hey yall,
Here's one to stump the band.
I must find a way to generate multiple records that consist of a ticket number as one field, and whether they have been used or not as another. The ticket numbers need to be sequential, with an increase in value of 1 over each previous record.
The ticket numbers will be seven digits long. Users, or their supervisors, must be able to generate a block of ticket numbers, by designating the first and last number, and thus they decide both the ticket number value range, and the number of ticket numbers to be generated at the time.
The difference between the first and last number might be a key to record generation, but then, can you use that to tell the system to add a specific number of records? And, other than appending pre-existing records,can you command the generation of records at all?
Thanks for any answers on this.
Generating Records
Jan 26, 2006I am trying to copy a field multiple times based on a number in an unbound field on a form. As well attach a consecutive number base on the number enter in the unbound text box.
Like this
[Table1] . [Field 1]
A70-3886-01
A70-7003-01
copy this many times unbound text box on form in to 3
[table2] . [Field1]
Spool Number
A70-3886-01-01
A70-3886-01-02
A70-3886-01-03
A70-7003-01-01
A70-7003-01-02
A70-7003-01-03
Generating Randoms
Dec 30, 2004I dont know what to call my problem but it's not much for experts, but too much for noobs like me. I've been trying to generate a random output in one of my forms which will get it's data from one of the tables that i made (Ex. frmForm1 will generate random data from Table1, and the fields are "Name", "Age", "Sex", etc.).. i've tried doing Randomize but it doesn't work. Pls advice, and thanks to whoever will give solution to this..
View 3 Replies View RelatedGenerating A Letter From MS Access
Jun 20, 2006I need to generate a pre-formatted letter from within MS Access, where the name, address and other information will be pulled from the current record on an Access Form.
Basically it is like a welcome letter I could send to any new client I enter into the data base. Since I am doing one letter at the time, mail merge in MS Word is not an option
What would be the easiest way to do this?
Thanks
Generating Order Numbers
Dec 12, 2006Anyone have have any suggestions on how to create personlised job numbers instead of relying on auto numbers?
At the moment I have to freetype them in when creating a record & I would like to work out how to automatically generate them.
maybe something like
1030:101
1030:102
1030:103
1030:104......and so on.
Any suggestions would be much appreciated
thanks. :)
Generating Invoice Number
Jan 15, 2007I have a small .mdb for invoicing. Till now I had to put invoice number, which had two parts, manually for the first part, for example 001 or 002 and automatically for the second part as Date part. So I had, for example 2006 or 2007. The complete number was, for example 001/2006 etc.
The problem was New Year. Now, all previous invoices from 2006 have 2007 extension and the first parts are not starting form 001 but they continue.
Generating Serial Numbers
Jan 16, 2007I've searched through the forum looking for what i want to do with no luck so hopefully one of you kind people could help.
I'm setting up a DB that logs in a batch of parts to which i generate a batch no.
As the batch progressess through the works when a problem arises we generate fault records (I've set up another table for this and linked the batch no fields together). I want to put a serial no. on this form consisting of 'year' 'batch No.' and ?'Auto number'? From reading posts is it best to put the 3 items in seperate fields (less hassle)
My 2 questions are
1, how do i extract just the year from a date field?
2, This is the main problem. The auto number. If i use auto number to generate a unique number to log the record the number will be massive in no time at all. This is fine as the unique identifier for the record in a table but not for the serial number of the printed report. Idealy I want to have the batch number then a serial number that resets to 1 on a different batch No. For example
21098/1
21098/2 Batch No./serial No.
21098/3
22309/1
22309/2
22309/3
Just some more info this database is a multi user database split front and rear.
I hope i've explained this well enough, many thanks for your help in advance
cheers
EQ
Generating Random Numbers
Apr 26, 2007Hi Guys,
In my table design view I've set my primary key to auto number and the 'New Values' to random. The problem is, sometimes the numbers generated are too long or negitive. Is there any way I can specify the limit of the numbers to be generated and keep them positive numbers?
Generating Unique ID From Combobox
Nov 2, 2007I would like to create a unique ID number (currently the Primary Key) for a table in Access 2000.
I am using 4 elements for the current manual entry, and want to automate it after I make one selection on the Combo box.
The elements are:
1: The species of the wood I am entering into the inventory.
2: The last two digits of the current year.
3: A ":" as a separator.
4: A sequential number unique for each species of wood as purchased in the current year. This would reset when the year changes.
For example, I can use the Combo Box to select either Ash, Maple, Myrtle or Elm. When I make the selection, I want the system to automatically generate the rest of the ID code.
If I had the sixth piece of Maple for 2007 I would select Maple in the box, then the generated code would be Maple07:006. The next piece of wood might be the fiftieth piece of Myrtle, so it would be Myrtle07:050. On Jan 1, 2008 the sequence numbers would all reset to 001 - so the first piece of Maple that year would be Maple08:001.
Can this be done? It would help me to avoid the problem of skipping numbers when I loose track of the last piece of the same species I entered. Can I do this with a simply formula macro, or would it need VBA?
And can this code ten be used in the Primary Key for that table - and will I need to re-enter all the 300+ pieces I already have in the table?
I had thought this could be done with a 3 field key - but I'd like to keep it as simple as possible.
Generating Same Records Only With Different Date
Apr 22, 2007HiI have a form where I am entering lot of records where only the date is different . In order to shorten this I need a querry that will (activated with a button on the same form) generate records in some other table (or in the same) with those records entered in the form and will have a field where will be put single dates in timeframe of starting and end date that I can enter in the form instead of single date.Thanks in advance for your time!
View 2 Replies View RelatedProblems Generating Table W/ SQL
Aug 8, 2007im trying to fill a temporary table, with the following SQL. While it successfully builds the table, its not pulling in any values. which are the important part. Can anyone see any problems?
'Dim stDocName As String
'Dim stLinkCriteria As String
Dim strSQL As String
strSQL = "SELECT TLOG.keyID, TLOG.Analyst, TLOG.EntryStatus, TLOG.Value, TLOG.FundingStrings, TLOG.Description, TLOG.PurchaseOrder, TLOG.Vendor, TLOG.FiscalYear, LPO.[PO Title], TAMD.Amendment, TAMD.DOR, TAMD.BCD, TAMD.FCD, TAMD.SSD, TAMD.AmdComments, TAMD.CertifiedDocument, TMOD.Modification, TMOD.MSD, TMOD.MRD, TMOD.SLD, TMOD.SCD, TMOD.OSD, TMOD.ModComments, TMOD.ObligationDocument INTO [tblSummery]"
strSQL = strSQL & Chr(10) & "FROM ((([lkup PO] AS LPO INNER JOIN [tbl Log] AS TLOG ON LPO.[Purchase Order] = TLOG.PurchaseOrder) INNER JOIN [tbl SupDoc] AS TSUP ON TLOG.keyID = TSUP.keyID) INNER JOIN [tbl AMD] AS TAMD ON TLOG.keyID = TAMD.keyID) INNER JOIN [tbl Mod] AS TMOD ON TLOG.keyID = TMOD.keyID"
strSQL = strSQL & Chr(10) & "WHERE TLOG.keyID = " & Me.lstResults.Column(0)
DoCmd.SetWarnings False
If tableExists("tblSummery") Then CurrentDb.TableDefs.Delete "tblSummery"
DoCmd.RunSQL (strSQL)
DoCmd.SetWarnings True
ps i learned my lesson about naming tables with spaces this morning.
Generating A Service Number Using VBA
Aug 18, 2005Hi,
I'm trying to make a database where a Date and a Service Number is entered into a form.
I have 2 buttons, one to automatically enter the Current Date and the second to automatically enter the Service Number.
The service number is incrimented by 1 each time and reset for every day.
eg.
Date Service Number
January 1, 2005 1
January 1, 2005 2
January 1, 2005 3
January 2, 2005 1
January 2, 2005 2
January 5, 2005 1
January 5, 2005 2
I already have the button that enters the Current Date. However, I am having trouble with the second button.
How can I code the button so that it searches the table for the Current Date and finds the record with the greatest service number for that date? After this is done, I want to create code that will automatically enter the next service number into the Service Number Textbox on the form.
eg. Using the Example Table Above
The date January 5, 2005 is entered into the Date Text Box and the Search Next Service Number button is pressed. The number '3' should show up in the Service Number Textbox
Sorry if its hard to understand, but thanks anyways.
I just realized that I placed this in the wrong forum, Sorry for the inconvenience.
Generating Special ID For New Record
Jan 6, 2006Ok here is the next step i need to figure out :)
I need to have unique ID for each of my record in my form, and it should be created automatically.It should look like this:
AB060106-1
AB060106-2
AB060107-1
AB060107-2
AB060107-3
AB060108-1
and so on.
Idea is to have two or three letters at the front that never change, then 6 numbers that represent current date (year,month,day), and then incrementing numbers for that day.Whenever day changes, this last number starts from 1 up to 999 and next day it resets to 1 again.
Any ideas where should i start from? Thanks
Generating A Query Result
Feb 9, 2006I want to know how to generate a result from a query in a control of a form? For exampel I have made a SQL count function in a query and I want to use this result i a control of a form.
View 1 Replies View RelatedGenerating And Loading Report
Jun 27, 2005Hi every body. I wonder how i can generate and load a report when a button is clicked on access 2000 form. I be happy if some one show me an example.Thanks
View 1 Replies View RelatedGenerating A History In Access
Feb 19, 2008Is there any way to leave a trail or history everytime a record is changed? We have kids in our database that sometimes go to follow-along when our service will not work for them at the present time. These kids go in and out sometimes several times. Since there is no absolute way of knowing, we have to create a new record everytime they come back to our service. I would like to be able to see a history of status changes along with some dates. Is there anyway to do this? If anyone knows, let me know. If not, oh well. Thanks guys.
View 6 Replies View RelatedQuery Generating Different Results
Jun 3, 2014If I use this query I get the results what I want
SELECT tblMainTWTTPSheet.txtRootCause, Count(tblMainTWTTPSheet.txtRootCause) AS CountOftxtRootCause
FROM tblMainTWTTPSheet
GROUP BY tblMainTWTTPSheet.txtRootCause
HAVING (((tblMainTWTTPSheet.txtRootCause) Is Not Null) AND ((Count(tblMainTWTTPSheet.txtRootCause)) Is Not Null))
ORDER BY Count(tblMainTWTTPSheet.txtRootCause) DESC;
But What I am trying to do is on a form select dates and generate data in this query. which doesnot happen even if I use the following query.
SELECT tblMainTWTTPSheet.txtRootCause, Count(tblMainTWTTPSheet.txtRootCause) AS CountOftxtRootCause
FROM tblMainTWTTPSheet
WHERE (((tblMainTWTTPSheet.date) Between [Forms]![frmStratificationOfRootCauses]![startDate] And [Forms]![frmStratificationOfRootCauses]![endDate]))
GROUP BY tblMainTWTTPSheet.txtRootCause
HAVING (((tblMainTWTTPSheet.txtRootCause) Is Not Null) AND ((Count(tblMainTWTTPSheet.txtRootCause)) Is Not Null));