473,836 Members | 1,514 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Auto Number mysql

I had an access database that i use as an ordering system. I have a
form for entering customer details. When i add a new customer on the
form the customer number is an auto number that appears when i type in
the details.

I have just moved over to mysql server with access as the front end. I
have setup the sql tables with the customer number as autonumber.
When i go into the form and add a new customer it does not generate the

customer Number automaticaly on the form like it did before. once i
have entered all the data and saved it i went into the customer table
to see if the data was saved the auto number was in there .
Is there any way for on the form for the autonumber to be displayed
when i start entering the data. Like how it was when i used access as
the back end before i moved to mysql as the back end
Any help would be great
Simon

Nov 7 '06 #1
3 12024
With Client/Server this is expected behavior. A reply to your other
post suggested a Stored Procedure, and that would be a good path from
an engineering perspective. Sounds like you want a quick fix in Access:

1. Create new macro with the following actions, saving to some name
like RecRequery:
Requery (NO control parameter)
GoToRecord with Record parameter set to Last

2. On the form in design view add a Command Button using the Toolbox.
BE SURE to have the Control Wizards enabled (magic wand icon
depressed)
In the wizard select Miscellaneous--Run Macro for category and
action
Click Next and select your macro, such as RecRequery.
Click Next and choose a Picture or type Text such as "Save Record"
Click Next and enter a name like cmdRecRequery
Click Finish

Now, when you enter data, and then click the button, Access will:
A. Save the record
B. Requery the table
C. Jump to the last record, which with an AutoNumber Key is your new
record

The drawbacks are that this won't work well if you have a lot of
records, or have a performance problem with the backend MySQL database,
or are in a busy multi-user environment. If that's the case, then some
coding or other workaround is needed.

-DHS-


S.*******@shos. co.uk wrote:
I had an access database that i use as an ordering system. I have a
form for entering customer details. When i add a new customer on the
form the customer number is an auto number that appears when i type in
the details.

I have just moved over to mysql server with access as the front end. I
have setup the sql tables with the customer number as autonumber.

When i go into the form and add a new customer it does not generate the
customer Number automaticaly on the form like it did before. once i
have entered all the data and saved it i went into the customer table
to see if the data was saved the auto number was in there .

Is there any way for on the form for the autonumber to be displayed
when i start entering the data. Like how it was when i used access as
the back end before i moved to mysql as the back end
Nov 9 '06 #2

After the insert you could also issue a query such as 'select @@identity
as id' to get the actual autonumber just created for your new record and
open the record with that id, or even nest it - "select * from my_table
where key_field=(sele ct @@identity)"

Another option which we have used before with old Paradox tables was to
have a table of key id's. Each row in the table stores the next ID for
a particular table. When you want to get an ID for that table, you just
read that particular row to get the ID to use and then increment the ID
and write it back. In a busy environment you would want to lock the
table during the two operations to ensure another client doesn't get the
same ID. Then you can safely display that ID on your form and use it as
the primary key when the user saves the record. This saves inserting a
new record which you have to later delete if they cancel out of the
form.

I prefer to use auto-increment fields and I just show the ID as 'TBA'
and after doing the insert use the select @@identity query to get the ID
and display it on the form (if required)

Jody
"Skarjune" <dh*@wordimage. comwrote in
news:11******** **************@ k70g2000cwa.goo glegroups.com:
With Client/Server this is expected behavior. A reply to your other
post suggested a Stored Procedure, and that would be a good path from
an engineering perspective. Sounds like you want a quick fix in
Access:

1. Create new macro with the following actions, saving to some name
like RecRequery:
Requery (NO control parameter)
GoToRecord with Record parameter set to Last

2. On the form in design view add a Command Button using the Toolbox.
BE SURE to have the Control Wizards enabled (magic wand icon
depressed)
In the wizard select Miscellaneous--Run Macro for category and
action
Click Next and select your macro, such as RecRequery.
Click Next and choose a Picture or type Text such as "Save Record"
Click Next and enter a name like cmdRecRequery
Click Finish

Now, when you enter data, and then click the button, Access will:
A. Save the record
B. Requery the table
C. Jump to the last record, which with an AutoNumber Key is your new
record

The drawbacks are that this won't work well if you have a lot of
records, or have a performance problem with the backend MySQL
database, or are in a busy multi-user environment. If that's the case,
then some coding or other workaround is needed.

-DHS-
>
Nov 10 '06 #3
@@identity is a SQL Server technique that can be used with an Access
Data Project, BUT that's not what the user is doing. The problem was
using an Access frontend with a MySQL backend, and being accustomed to
seeing Access provide an Autonumber in the form--even before saving the
record.

Using a bound Access form with MySQL is not the best idea, but it will
work for the most part in a LAN workgroup situation. For example, the
form won't refresh automatically with a Save; a requery has to be
forced.

WIth MySQL, LAST_INSERT_ID( ) is a SQL function that will return the
value for a the most recent AUTO_INCREMENT value for a specific
connection. HOWEVER, most situations in Windows do not maintain a
connection (ODBC pooling?) so you only get back zero. To effectively
use LAST_INSERT_ID( ) you need to run a batch script that can maintain
the connection while using the ID for child records.

In short, simple Access forms bound to MySQL cannot be fixed in a
multi-user environment to display a new auto ID--instead a solution
needs to be coded to handle the request.

-David Hedrick Skarjune

Jody wrote:
After the insert you could also issue a query such as 'select @@identity
as id' to get the actual autonumber just created for your new record and
open the record with that id, or even nest it - "select * from my_table
where key_field=(sele ct @@identity)"
I prefer to use auto-increment fields and I just show the ID as 'TBA'
and after doing the insert use the select @@identity query to get the ID
and display it on the form (if required)

Jody
Nov 14 '06 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
2722
by: Sims | last post by:
Hi, I have a table that give a unique ID to certain Articles. To give the ID i simply use the Auto count of the DB. But what happens when i delete a record? i am now missing an ID. How can i insert a row and give it the first available number? For example ID 1, 2, 4, 5, 6
0
1683
by: TheMechE | last post by:
Has anyone had a table that has lived long enough to wrap the auto incrementing number...? i.e. the complete byte span and back to 0x32. I'm just wondering if mySql will fill in the deleted ID's that don't exist, or if it just halts... --
12
5680
by: Nick | last post by:
Is it possible to have multiple auto increments in one column? Say I have two tables... 1. table 'messageboards' with fields ('id' , 'name') 2. table 'messagethreads' with fields ('id' , 'messagethread' , 'messageboard_id') The 'messagethreads.id' column could have multiple auto increments. Then I could easily query a messageboard with ("SELECT * FROM
3
2163
by: Smitro | last post by:
Hey, I have a site that is about to go into production and it has a couple of tables that it uses that could get quite large once in full swing, most of them have a column called "id" and it auto increments every time a new row is added. When setting this up, what do you recommend using at the column type and size. atm I have gone for a big-int with the size of 20. I wonder over time
2
3064
by: x0054 | last post by:
Ok, this is a stupid problem, I admit. I have a scrip that adds records to a table. The records are for photos. So, after adding a record the scrip also uploads a picture from users computer and renames it to {$recordID}.jpg. The problem is, my recordID field is auto incrementing in mySQL. So currently I just take the last added record and assume that it the one I just added, and rename the file to the recordID of that record. It works,...
1
2367
by: daniellee2006 | last post by:
I am creating a basic website to store people profiles and within this website i have a page that creates a table dependent on the number of records in mysql written in PHP within these tables they show images i need these images to hyperlink to other HTML pages... Note these tables are written within the <?PHParea of the full Script
13
4728
by: S.Dickson | last post by:
I had an access database that i use as an ordering system. I have a form for entering customer details. When i add a new customer on the form the customer number is an auto number that appears when i type in the details. I have just moved over to mysql server with access as the front end. I have setup the sql tables with the customer number as autonumber. When i go into the form and add a new customer it does not generate the
2
7367
by: S.Dickson | last post by:
I have just moved over to mysql as the back end and keeping access as front end. My database is for a ordering system. I use autonumber as the Order Number. as mysql does not display the order number (autonumber) until the form has been saved. Is there a way on the order form that i can have a button that will save the data then reopen the form so the order number ( autonumber is displayed) I am currently only learning about access...
0
1672
by: Tom | last post by:
I'm using the DB_DataObject script createTables.php to auto-generate the necessary database schema on two databases. Using the .ini approach (not the in-line PHP approach) to configure DB_DataObject in my script. Everything is by the book, but I'm having a big problem: Here's my main.ini file: database_one = mysql://user:password@localhost/one database_two = mysql://user:password@localhost/one
1
1972
by: cool84 | last post by:
hai, 1. i need help on how to set the auto incremental as "REF0001","REF002","REF003",.......and so on. 2. i declare the field as integer but i want the prefix "REF" follow by the increment number . 3. I want every time the user insert their data via the php form the reference number automaticly insert in the mysql database.
0
9671
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10257
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9387
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7794
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6981
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5651
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5829
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4459
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
3116
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.