473,385 Members | 1,564 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,385 software developers and data experts.

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 11961
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=(select @@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.googlegr oups.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=(select @@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
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...
0
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...
12
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' ,...
3
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...
2
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...
1
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...
13
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...
2
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...
0
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...
1
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 ...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 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 former...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

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.