Auto Number mysql - Microsoft Access / VBA

Simon

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 6 '06 #1

Subscribe Reply

2195

Neil

I use SQL Server, but I believe the principle is the same. You won't get the
autonumber value until the record is saved. Since it's not generated by Jet,
Jet can't anticipate what its value will be, and can't display it until it
gets it from the back end engine.

One thing you can do as a workaround is to create the record first, and then
bring the user into it. In other words, instead of just going to a new
record, create a new record in code, get that autonumber value, requery the
form, and then go to the new record for the users to input the other fields.
The users might not like that, as they might just be used to going to the
"new" record; but it may be the way you need to do it if you want to display
the number before the record is edited and saved by the user.

Or, if the issue is that the users are getting confused by there not being a
number, then simply hide or disable that field until a value appears in it,
or something to that effect.

"Simon" <S.*******@shos .co.ukwrote in message
news:11******** **************@ e3g2000cwe.goog legroups.com...

>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 #2

Granny Spitz via AccessMonster.com

Simon wrote:

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

Jet creates a new autonumber when the new record is created, but MySQL
creates this number when the new record is committed. If you want to see
this new number, you need to save the record first so it's assigned to this
record. You may be able to bookmark the record and move back to it with the
recordsetclone after you save the record, but I've never done this myself
because it's always hidden on my forms when I use an autonumber column. It's
entirely irrelevant and my users don't need to see it.

--
Message posted via http://www.accessmonster.com

Nov 7 '06 #3

Similar topics

2716

Database Auto Count what about delete?

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

PHP

1681

What about auto number primary key - wrapping?

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... --

MySQL Database

5673

Multiple auto increments in one column?

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

MySQL Database

2162

MySQL Auto Increment

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

PHP

3062

reading auto increment number before it is written?

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,...

PHP

2364

Create Hyperlinked Images within a Auto Cascading Table PHP

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

PHP

4728

Auto Number mysql

by: S.Dickson | last post by:

MySQL Database

7362

Auto Number with my SQL

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...

MySQL Database

1671

PEAR DB_DataObject auto-generator multiple databases

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

PHP

1970

mysql auto increment

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.

PHP

9618

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...

General

9454

Changing the language in Windows 10

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,...

Windows Server

10038

The easy way to turn off automatic updates for Windows 10/11

by: Hystou | last post by:

Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...

Windows Server

8933

AI Job Threat for Devs

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...

Career Advice

7456

Access Europe - Using VBA to create a class based on a table - Wed 1 May

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...

Microsoft Access / VBA

5354

Trying to create a lan-to-lan vpn between two differents networks

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...

Networking - Hardware / Configuration

5482

Windows Forms - .Net 8.0

by: adsilva | last post by:

A Windows Forms form does not have the event Unload, like VB6. What one acts like?

Visual Basic .NET

4007

transfer the data from one system to another through ip address

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

C# / C Sharp

3609

How to add payments to a PHP MySQL app.

by: muto222 | last post by:

How can i add a mobile payment intergratation into php mysql website.

PHP