DMax Vs Autonumber - Simple Solution?
Mar 7, 2005
I'm reading lots of threads about various problems with autonumber, and I encountered a corruption in a very beginning database myself, using the autonumbered field as a sequential file number, my primary key. So, my question is.....how is the very most simple, NOVICE, way to use DMax (step by step on what to write and where to put it) to create a sequential file number, starting from 00001 and ad infinitum until I go out of business :)
I simply want a field called "file number" that will effectively auto-number, without the dangers of autonumbering crashes etc.
Thanks for your suggestions!
View Replies
ADVERTISEMENT
Jul 23, 2005
Hello Access friends,
Trying to have a sequential autonumber for the ScreenID with the DMax () function.
Please advise on what is wrong with the following :
=Nz(DMax("[ScreenID]","[Screenprep]","[ScreenID] = '" & [CarModel] & "-" & Left$([Category],1) & "'")+1,0)
Neither putting this code in control source or beforeupdate event of the form is not working.
I have looked around and from previous posts in the forum come up with this module. But it is not working either:
Public Function NewScreenID() As String
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim ScreenID As String
Dim CarModel As String
Dim Category As String
On Error GoTo Err_Execute
Set db = CurrentDb()
Set rst = CurrentDb.OpenRecordset("SELECT Max([Screenprep].[ScreenID]) AS MaxScreenID from [Screenprep];", dbOpenSnapshot)
If IsNull(rst!MaxScreenID) Then
NewScreenID = [CarModel] & "-" & Left$([Category], 1) & Format(1, "0000")
Else
NewScreenID = rst!MaxScreenID + 1
End If
rst.Close
Set rst = Nothing
Set db = Nothing
NewScreenID = ScreenID
Exit Function
Err_Execute:
'An error occurred, return blank string
NewScreenID = ""
MsgBox "An error occurred while trying to determine the next sequential number to assign."
End Function
In advance thank you for your time.Can someone please guide me on how to sort this out?
View 1 Replies
View Related
Jan 1, 2007
Hi the specialists.
I do not want security on any of the files in my computer.
I'm placing this in "general" because I'd like a direct and practical answer, please. And if there is none, please say so. Computers are immensely complicated and I am just a little bit tired of people superciliously refering the uninitiated to "Microsoft's FAQ's" That source is to most normal humans as obscure as the software programming itself.
I have also searched this source and Google for hours, without getting an intelligible answer.
This is my problem:
I am the only user and administrator on my computer.
I have a back-end file which is easily accessed through the front-end.
.... but
I cannot open the back-end to access the tables directly.
I get the following error message:
You do not have the necessary permissions to use the 'C:Documents and SettingsAll UsersDocumentsAccessThingsWORKLOG_be.mdb' object. Have your system administrator or the person who created this object establish the appropriate permissions for you.
Of course the person who created this back-end is me, but I have no clue what I did, because it is at least two years since I created it.
Could someone please help?
I have tried to use the "shift", click method. It does nothing - just gives me the same message.
Pierre.
View 4 Replies
View Related
Oct 3, 2006
Hi all,
Please forgive a newbie that asking the stupid question.... i just wonder is that anyway to set the date format to short date with instead of mm/dd/yyyy to dd/mm/yyyy to let the user to keyin?
Thanks alot.
View 3 Replies
View Related
Mar 7, 2007
My database contains documents which are entered randomly (i.e. - not in any particular order). But then, for my reports, I must show these documents in “Document Number” order, once I have gotten them in the order I want (usually by date or other criteria).
In Access 2003, (in query mode) all I had to do was enter the number 1 in the first “document number” field, then number 2 in the second document number field. Then, by pressing and holding the “down arrow” key, this field automatically filled in the document number consecutively from 3 through whatever number of documents I have in my database. Very quick and efficient. I’ve had Access since the very first edition (1996 I think), thus I don’t remember how I was able to get the “Document Number” field to “self-fill” in the query mode, but have been doing this for years.
Now, I cannot get Access 2007 to do this, no matter what I do, thus I am forced to manually enter the number for each document.
Question: How can I get the “Document Number” field to fill-in the series automatically (in query mode) by pressing and holding the ‘down arrow’ key? As I said, the information is entered at random and has to be sorted later by document number, thus I cannot use a Primary Key or AutoNumber for this.
View 4 Replies
View Related
Jan 31, 2005
Hi
I was wondering if anyone can help me.
I am trying to build a database as part of my IT A-Level coursework, but I have little experience with Access. Although I have built the main part of the database, I am now stuck with something which I am sure is quite simple, if only I knew how to do it!!
I have got four tables at the moment, Suppliers, Category's, Products and Orders. From these I have made a form which will be used to put in new orders, and it works so that when it is filled in, the "order" is automatically entered into the Orders table.
My problem is that in this form I have drop down boxes for category name, supplier name and product name, which are looked up and taken from the respective tables. I want to have it so that when I select a category from the category drop down list, it automatically limits my choices in the suppliers list, and offers me only the suppliers which supply products in the category I have chosen. Then the products list should only be the products from the selected supplier.
I apologise if that makes absolutely no sense at all, it is very difficult to explain by writing it down... my teacher can not help me, she appears to know even less about databases than me, and I have had to teach myself the very little I know! I know this must sound very pathetic and trivial, but I just can not make it work! Any help would be greatly appreciated!
I also have another slight problem in that I can get my form to calculate the total cost of the order, by taking the price and the quantity, but then this value does not appear in my orders table.
Thanks for taking the time to read this and helping me if you can!! I am really grateful!
Charlotte xx
View 3 Replies
View Related
Nov 21, 2013
I have two tables linked to each other in one to many relationship. Instead of auto number, the date and shift (Text) is being used as the primary keys (Composite Primary Key). Here is the tables structures,
Payouts Table:
Date: Primary Key
Shift (Day or Night) : Primary Key
Bills Table:
Date: Primary Key
Shift (Day or Night): Primary Key
Autonumber: Primary Key
The tables Payouts and Bills has one to many relationship. One payout row can have many bills. The problem is that I want to start the Autonumber in bills table everyday from 1. As date and shift are different for every day so even if i start bills from 1 everyday, it wont make same primary key. I can do it manually but I want to make it automatically.
View 4 Replies
View Related
Feb 12, 2008
Hi,I'm looking for a bug/issue tracking solution done entirely in MS Access. Does such a thing exist?My requirements are that it must need only Access, and be accessible in a shared environment solely by opening a .mdb file from a shared folder. It must support various issue lifecycle related things, and the stuff those tracking systems do in general.It may or may not be commercial software.If anyone knows of such an available solution, please let me know.(And yes, I've searched on Google, and haven't found anything worthwile, so that's why I'm asking here now.)thx
View 5 Replies
View Related
Jul 26, 2007
Hi,
I haven't worked with Access for a while, now i'm working on a project and just can't handel with a calculation. I have somewhere the solution for my problem, I had use it other times, but now i just don't know where to find that sample database.
I have 2 tables: tblProjects and tblCosts
tblProjects
ProjectID
ProjectName
ProjectValue
...............
tblCosts
ProjectID
CostName
CostValue
................
What I want is to calculate the Benefit=ProjectValue-CostValue.
I know it is possible, in other cases I have used some union queries, sum calculation and I had my results very simply. But now, as I said before, can't find that piece of SQL. :(
Any solution is appreciated.
Thank you
View 3 Replies
View Related
Feb 12, 2008
Hi,
I'm looking for a bug/issue tracking solution done entirely in MS Access. Does such a thing exist?
My requirements are that it must need only Access, and be accessible in a shared environment solely by opening a .mdb file from a shared folder. It must support various issue lifecycle related things, and the stuff those tracking systems do in general.
It may or may not be commercial software.
If anyone knows of such an available solution, please let me know.
(And yes, I've searched on Google, and haven't found anything worthwile, so that's why I'm asking here now.)
thx
View 4 Replies
View Related
Jun 8, 2005
I need to try and create a simple form that a user enters data into and then hits a print button and the text they entered is printed in a particular way.
i.e. they type in someones name, job and company into 3 fields and then hit a print button and this then prints :
PERSONS NAME
JOB TITLE
COMPANY
We also need the print to be formatted a particular way but that is another issue
This is for a small exhibition we are trying to run and we need something to print visitor badges with
Has anyone got any ideas that can really help as we have been let down by someone who was going to do this for us
View 3 Replies
View Related
Mar 14, 2006
This is for anyone who has made a form with a lot of check boxes and wants to make a report out of them thats decent.Hopefully this simple example file is enough to assist people.Keywords:Checkbox Checkboxes report check boxes box
View 1 Replies
View Related
Jul 8, 2005
Link to the original thread (http://www.access-programmers.co.uk/forums/showthread.php?t=89557)
I have realized what I was doing wrong and thought that I would post the solution in case anyone else does the same trying to implement security. First off thanks Pat Hartman for the input. You were right on there needing to be a cutoff and now one is added that ensures they can't edit punches after payroll has started.
I had the right idea with the many to many relationship to get a list of buildings. What I was doing wrong was joining the resulting table to the shifts table. Instead the correct way (well, it works anyways) is include WHERE Building IN (SELECT ....) in the sql where the select statement gets the list of buildings numbers that I have access to.
Now the list is limited to the buildings that they have access to and when you delete only the shift table is affected because none of the other tables are joined.
View 1 Replies
View Related
Dec 23, 2005
Hi Everybody. I've been nosing around here because I have this difficult problem with a database design. I modelled my program in UML so I have a class diagram. Now I want to create an access database out of it, but this is too hard for me.
It's about a school project for flowers. Every year, they make a cross plan. This cross plan contains crossings. Many crossings. And every crossing exist from 2 genotypes. A mother and a father. With this crossing several new genotypes are created. How on earth do I realize that in a database? A plant is male as well as female so you don't need to indicate which sex it is.
Further more I want a genotype to be judged on his characteristics by a user as much as he wants to.
Well...I hope someone can help and if you have questions about it don't hesitate to ask.
Thanks in advance!
Gr Billy
View 4 Replies
View Related
May 12, 2005
Good afternoon all,
I have a A97 Db. On one of my forms (see attached screen pic) I have a field "Payment type" for either Cheque, card of Account. There is no code or functions behind it, it just stores a value.
Trouble is my users keep forgetting to fill it in!
My first (and easiest) solution would be to make "Payment type" a required field. However. The field will only be filled out under certian conditions.
That is if the "Status" field value = "X" (drop down box holding two values "X" and "Y").
What would the code be? I presume it would be in the Form "after update" field? Would read somthing like:
if [Status] = "X"
and
if [PaymentType] = "Null"
then
mssg box "Please enter payment type"
I have a vba book on order to start learning, but as you have probably guessed, I have not received it yet! :)
Any pointers or info would be much appriciated. Many thanks :)
View 1 Replies
View Related
Jun 29, 2005
I need a little bit of advice on this one.
I have 2 tables that are used for different things, one table, denial data, is used for tracking all requests. It is updated with the form, Daily PAs. On the form are 2 buttons, each running a macro. One button exports the data to Excel by running a query to specify the date range. The second button is my problem. It also has a macro, with an append query, to append only requests that have been denied to my second table, Denials, to be later updated with additional information. The append isn't working because I am getting three different errors, a "type conversion failure," "key violations", "lock violations" and "validation rule violations." Now, I know I can begin working out these violations and get it working, but I'm sure that involves a lot of time and coding. I would really appreciate any other suggestions to accoplish the same tasks. I need to keep the approved requests seperate from the denied requests for auditing purposes. Thank you for your help.
View 7 Replies
View Related
Dec 30, 2004
Hello All
I am in need of a lot of help. The situation is as follows I have a table with users that have certain classes that they have to take and in another table I have the dates that these classes are offered. My problem is I want to find a way to map all the students to their required class by scheudling them into the required classes taking into account date conflicts and classes required before taking a certain other classes. I guess my question is if there is any possible to do this in access without me phyically having to schedule each users required classes to the correct time making sure there are no date conflicts. Any help would be highly appreciated because we are talking about 3000 users that need to have schedules and that is extremly time consuming if I have to sit here and do the schedule for each user. Thank you in advance for your time
View 5 Replies
View Related
Mar 19, 2006
I want to use a counter increment so that I can loop F1 to F3 I don't want to create 3 (actually I'm trying to avoid creating 50) If/EndIf blocks of code. Can someone help me?
' Initialized memvars
F1 = "PO_Num"
V1 = "0001"
F2 = "Vendor_Num"
V2 = "0002"
F3 = "PO_Num"
V3 = "0001"
TableName = "MyTable"
QueryStr = "UPDATE " & Tablename & " SET "
mCtr = 1
Do While mCtr <= 3
If Not IsNull( Eval("F" & Trim(Str(mCtr)) ) Then
QueryStr = QueryStr & Eval(FValue & " = '" & Eval(VValue) & "';"
End If
mCtr = mCtr + 1
Loop
The way that it works:
Do While mCtr <= 3
If Not IsNull(F1) Then
QueryStr = QueryStr & F1 & " = '" & V1 & "';"
End If
If Not IsNull(F2) Then
QueryStr = QueryStr & F2 & " = '" & V2 & "';"
End If
If Not IsNull(F3) Then
QueryStr = QueryStr & F3 & " = '" & V3 & "';"
End If
mCtr = mCtr + 1
Loop
View 1 Replies
View Related
Aug 18, 2006
I need help on an update query. Case is an automatic letter generation for particular document which has revisions like 00 or 01. So when this is done, on click, open an update query to update date and letter no in main table against that particular document. I made the query but does not work and says result should be an updatable query. I am posting SQL below.
UPDATE [MDL-10], TRANSMITTALGEN, [Transmittal Record Query] SET [MDL-10].[REV 00 SUBMISSION] = [Transmittal Record Query]![MaxOfTransmittal Date], [MDL-10].[REV 00 SUBMN LETTER] = [Transmittal Record Query]![MaxOfTransmittalNo]
WHERE (((TRANSMITTALGEN.REV)="00") AND (([MDL-10].[Select])=[Forms]![TransmittalGeneration]![Text2]));
Please help
View 1 Replies
View Related
Nov 4, 2006
I have almost finished my current database but I was asked to create a log table/log file that would list changes made to every record. Now my current database don't allow duplicate records, so any advice pointing me into the right direction will be helpful. I have ran through the search area and found nothing that I can use. Can any one help me out in this specific problem. I picked up a few books and none of them give examples of such things. Thanking you all in advance...
View 14 Replies
View Related
Oct 11, 2005
Hi there,
I have built a Access solution for a music school, It was installed on 3 machines.
I'd like to protect my database from installing onto another machine without my permission.
I did install database as a mde file so they cannot see my codes. However, if they copy the database to another machine (esp. another machine in different school) they can use my software without my permission. How can I prevent this? If they copy the mde file into unauthorized machine, database should work as a demo version (such as limiting the number of records in tables to 10). How can I do this? What should I check, hd id, mainboard serial or what? Is there any ready solution (at least modifiable) for that kind of problem?
regards
View 14 Replies
View Related
Mar 7, 2006
I have searched the forum but have failed to find the answer to my problem. I have a front end ms access 2000 solution that I distribute to user PC's with an MDE back end data base on a server.
I now need to release a new version that includes changes to forms, queries and tables.
However there is data in the original mde data base I need to retain. Is there an easy method to migrate that data to the new data base. I have changed some relationships but this should not affect data integrity - most change is related to adding new fieldsto existing tables or new tables (no previous data).
If I create a new empty mde will I be able to import old data into it from previous mde?:confused:
View 2 Replies
View Related
Apr 29, 2008
I have unique situation not sure how to achieve this in Query.
For understanding I have attached mdb has following tables
'PR_RPT'
'SYS_RPT'
'TIME_RPT'
Final_PR_RPT
'PR_RPT' and 'TIME_RPT' has unique ID called as 'PTUNID', My Final goal is Final_PR_RPT.
I have created a Query1, which Look into 'PR_RPT' and 'TIME_RPT' and does a match on 'PTUNID' and populates all the 'SUM HR' Field Values from 'TIME RPT' table to matching 'PTUNID' in Column 'SUM HRS' similar to Final_PR_RPT,
In Addition to that what I am trying to do is
1- Look for PTUNID from PR_RPT to TIME_RPT if that doesnot match then
2- take Unmatching 'PTUNID', Look for which 'Project LEAD' owns the 'system ID' From 'SYS_RPT' Table then
3- Populate the unmatched 'PTUNID' 'SUM HRs' from 'TIME_RPT' Table against that 'Project LEAD' which Owns that unmatched system and has same ProjectID in PR_RPT in new column as Wrong SUM HR and WRONG SYS ID
Final_PR_RPT, shows how the result should stored.
I tried using IIF Statement but I believe I am not doing it right.
Thanks
Niri
View 4 Replies
View Related
Sep 15, 2006
Thanks for all the help to date.
Problem trying to get a dmax field for an invoice no in a query - am doing something wrong current attempt below won't work
Expr1 :dmax([Invoice no],[Table name]![Invoice no])
what am I missing - do I need something on the end when I get this to work I will use it to add 1 for the final query prior to print
View 4 Replies
View Related
Dec 21, 2006
Okay...I know the basic syntax for Dmax (or at least I thought I did) but I cannot get this statement to do what I want!
In the <before update> event I tyoed this:
Dim RNum As Long
Me.txtRNumber = DMax("[Request_Num]", "test_request") + 1
Me.txtRnumber = RNum
txtRNumber is the field on the form
Request_Num is the field on the table
test_request is the table
yes...I know the name syntax is bad (it's an older db I'm using as a test for a newer one).
Obviously, I want to automatically increment the field value by 1 on every new record (an if newrecord clause is included). But I just can't get the stinkin thing to work!!!
View 12 Replies
View Related
Oct 3, 2006
I am having difficulty understanding under which circumstances I should be using Max() as opposed to DMax() functions. Both seem to do the same thing (find maximum value in a field), but DMax has additional arguments.
What I currently want sounds like neither of these, as I want to query the maximum of several values in various fields for a specific record, rather than the maximum of several records in a specific field. Is there a way of doing this otherwise than by nesting IIF functions several deep?
I keep running into "Microsoft Office Access has encountered an error and needs to close" error messages, and am coming around to the view that I may need a UDF. I have never done one in Access before (but did some time ago in Excel).
Currently I have fields in various tables:
T_Clients.Commencement
T_Clients.Cessation
T_Tasks.StartDate
T_Tasks.EndDate
There is a one (T_Clients.ID_Clients) to many (T_Tasks.ID_Clients) relationship
T_Clients.ID_Clients and T_Tasks_ID_Tasks are primay keys.
In "pseudocode" my query, probably via a UDF, needs to return:
Min(Max(StartDate,Commencement)+12months,Min(EndDa te, Max(Cessation,StartDate))+9months
Commencement and Cessation may contain Null values, which are then to be treated as #1900-01-01# for the purposes of any comparisons.
My first attempt at this was to create several queries, each based on the previous query. Inefficient, perhaps, but I could trace the logic. I started by creating queries that substituted #1900-01-01# for null values in the Commencement and Cessation fields.
However when I got about 3 queries deep I ran into the "Access has encountered an error and needs to close" error, and no amount of playing around with it stopped that.
I don't know if relevant but I don't *really* have a T_Tasks.StartDate field. I have a query that calculates the start date from the previous EndDate, but I did not want to complicate the problem specs. That query seems to work ok. If I get a solution to the above I hope to jigger it to fit.
View 3 Replies
View Related
