Hi i have to design a member database in access,
As the membership number i need to use the first letter of the surname,
then have an auto number.
e.g Smith, if the last member was S47 then Smith would need to be S48,
This will be used on a form for a video hire shop, so, the customer details
will be entered, is it possible for this auto number to work just by putting the name in
the surname field?
Here is my issue. In a table with an Auto Number index some records have been deleted. I have been able to recreate them along with their original auto number. The problem is that I do not know how to append these records forcing the original auto number. I have tried changing the auto number field to a number field in the table, this works except I cannot change it back to auto number.
I am sure I’m not the first with this question or issue. I did search through a couple hundred entries about auto number before I posted this question.
I have a query that I try to update to Alphabetical instead of numerical. I am using MS Access. on the row say "Update to" I Enter Alphabetical letter but it doesn't work. I wonder if any way to do so. Please HELP! Thanks
I was hoping someone could help me with a validation rule for a table.
What I require is for you only be able to enter specific data, for example the first character must be a number and be 7, 8 or 9, and the second character entered must be a letter, only letters available are A, B, C, D, E, N and S. For example these will be accepted 9C 8D 7B 7S 7D 8E, other will not (3S, 7G, B8)
Can anyone tell me what it is i enter into the validation rule box or direct me to any online help with this.
I am trying to use access to connect to SQLServer with an insert query. I need to generate alphanumeric number for each record. The letter will move to the right as the alphanumeric number increase.
For example: A0001 , A0002.... -> A9999 0A000, 0A001 ... => 0A999 1A000, 1A001 ... => 1A999 2A000, 2A001 ...2A999 9A000,9A001 ...=>9A999 00A00, 00A01 ... => 99A99 000A0, 000A1 ... > 999A9 0000A....=> 9999A Last number will be 9999A
I would like to know if there is any procedure to restrict/stop auto number increment for certain number of record count (say 50), then increment by 1 for next 50 records.
I'm trying to get the maximum number in a table field to increase it by one depending on the member that is selected in a drop down in a field.
I have three tables: members, programs and times. Each member can have N programs and each program can be broadcast N times.
Each member has a three digit code, like XXX. Each program has the three digit code of the member + three numbers that are supposed to auto increment. That is, the first program of member X with the member code XXX is called XXX001.
What I'm trying to do is that when a new program is filled in and I select the member, then the program code should update automatically, adding one to the latest program by that member.
That is, if the last program by member X that was inserted in the database is XXX010, then if a new program is inserted it should automatically be XXX011, even though programs by other members have been added in between.
This is the code I use now, for the AfterUpdate when selecting the member in a dropdown in the form. But although I've played around a bit, I just get error messages...
Private Sub medlemsruta_AfterUpdate() Dim medlemskod medlemskod = Me![medlemsruta].Column(2)
Dim strMax As String
strMax = DMax("programs_kod", "table_programs", "Left$(programs_kod, 3) = medlemskod") Me!program_kod = Left$(strMax, 3) & Format$(Val(Right$(strMax, 3)) + 1, "000") End Sub
Medlemsruta is a dropdown where one selects the member from the members table, where the three digit code is in the third column (Column(2)).
I'm trying to use DMax to get the maximum number for the particular member and after that adding 1 to that for the new program code.
I'm trying to get an invoice number field to auto generate the next number, keeping the format as "00000"...this is what I have, which gets the next number but drops the leading 0
Code:
Private Sub Customer_AfterUpdate() If Len(Me.[InvoiceNumber] & vbNullString) = 0 Then Me.[InvoiceNumber] = (DMax("[InvoiceNumber]", "[tblInvoiceNumber]") + 1) DoCmd.RunCommand acCmdSaveRecord End If End Sub
invoice numbers are 04024, 04025 etc...how I keep the formatiing?
I have a table with an auto number PK. This table will contain orders. I'd like to use the PK from this table as the Invoice number on the invoice. I'd like to have it start at a number other than "1" just because it looks better on an invoice. I don't know how to do this. I looked at the table design to see if there were options available to me there but couldn't find anything. Is it possible? (I do not know how to use code.)
I'm designing a database in which the primary key is a combination of the first letter of first name, the first letter of last name and the date of birth.
The title probably doesn't give an idea of what I need, so here goes.
We currently have reference #'s for our bills that include the date requested in mmddyy format, the first 3 letters of the customer, the initials of the order taker and finally a sequential number to show the sumber of bills that day.
example: 120705SEAGMM02 2nd order taken by GMM for Seagate on 12/7 120705SEARLH01 1st order taken by RLH for Seagate on 12/7
I want to generate this number automatically based on the date entered and the initials given of the user. We only deal with one customer at our desk so that will always be "SEA".
I have a query that generates the first portion (date, customer, and initials):
SELECT Format([REQDATE],"mm") AS [Month], Format([REQDATE],"dd") AS [Day], Format([REQDATE],"yy") AS [Year], Format([REQDATE],"mmddyy") AS [Both], [Both] & "SEA" & [PickUpReqData]![INITIALS] AS REFNO FROM PickUpReqData;
But I can't seem to get my head around the part of generating the number. I know I had done this in a database I created 2 jobs ago but all my files were flooded out in Katrina. (I have since relocated to Atlanta, although I'm not looking for sympathy. But I will take what I can get!)
Anything to point me in the right direction will be appreciated. I am looking to have this on a form and feed the REFNO field in the PickUpReqData table.
I have a table called "OrderDetails" with following fields:
Num OrderID (Primary key) Product Quantity Price
I want to create a data entry subform that can used to enter order details in this table such that, for a given OrderID, the Num field is automatically set to previous number + 1. For example, for OrderID = 12, if there are 4 products that need to be entered, the 4 records should automatically take 1 , 2, 3, 4.
ok here is what i want.. imagine a search box, just an empty text box that allows the user to type things into it...
there will be a simple table from the database behind it, here is the typical structure of a record
<Country>Poland <Price1>60 <Price2>85 <Price3>95
in this table there will be say a 1000 of these records... my user is going to have to quickly search through these records while on the phone to a customer, and quote one of the relevant prices associated with that country
what i would like is this most convenient system.. as the user types in the letter 'P' just below all the records with countries that begin with 'P' are displayed (with a scroll down arrow if needed)..as well as the 3 relevant prices with that record
if they then type an 'O' into the box (which will now hold 'Po') all the records with countries beginin with 'Po' will be displayed (eg Poland)
if they delete the 'o'. once again the displayed records below the box will return to just the countries begginin with 'p'
all that is needed is for the records to be displayed, thats it.. but the adding and deleting of the letters within the text search box will need to instantly manipulate this list..
i have posted on other forums, and have been told this can be acheived in access, is it hard?
I have a form with an Auto number field. When the form is opened in new record mode the auto number field displays the following: (Auto Number) instead of the actual number that it has generated. I want the number to be displayed
Hi, I have a form with some fields on it, there is one called Pro Number. what I would like to happen is when a new record is created, it starts at a certain number and continues to increment by one. In other words, the first record would be 5600 and the next new record would be 5601 etc.
I did have this working in an previous database, by creating a append query to start the number, but that does not seem to be working now...
I make a data base every year for work orders that my work does for our customers, I have every thing set up and it looks great except for one field, last year I was able to make it do an auto number once I put the date in. for example first box work Order # ___ second box date once I put in the date an work order # would fill in the w/o# box and it would follow what ever the last work order # was (12600). for example I need the year 2006 TO START OFF WITH # 12601, What querie/option do I need to accomplish with this info.
I have an access data base and every year I (only my second year making it-ex-employee use to make it) need to make a new one to start off the year for all our new work orders.
but any way I have the form made looks exactly like the previous years but I can not get the work order box to become an auto number and I need it to tart of at 12601 and I had it so that when I put in the date the next work order # would pop in the box, and I need to get all the hours and part totals to add up.
I have 47 records in a table that were numbered 1-47 with auto number. I arranged the records in order of date increasing, and now, the auto numbers are all jumbled up. I want to have autonumber re-number the table 1-47 with 1 being the earliest date and 47 being the latest. Is there a way I can do this with autonumber? I need to continue using auto number, so I cannot simply just delete the field, and re arange the numbers manualy. what I tried to do was, delete the old auto number field, arange the table in order of date increasing, and then put auto number back in the table. All that did was give all the records their old numbers that auto number had previously given them. It's like access will not forget the numbers auto number had assigned to them.
Hi I have a question connected with new start of counting of Auto number filed in a table. I just want to start numbering from the beginning in easy way. Thanks!