Inserting data from an Access form to a specific point in a table

[Aussie1127]

I need to find out how to add something from an Access form to an Access database without appending it to the end.

In my database table, i have two columns that are needed, one stores all the passwords for a specific program (somewhere in the vicinity of 3000 of them) the other column stores a list of organisations next to their passwords.

I need to find out what command, and how to insert a new organisation in the next available slot that has a space next to the passwords.

I know i need something like :
"SELECT tblAust.Organisation FROM tblAust WHERE (Organisation='' AND NOT password='');"

Here is an example of what i mean :


Password Organisation

0001a IBM
0002b McDonalds
0003c Oakley
0004d <-- Insert here
0005e
0006d
0007f
...
3000z_____________________________________________
Appended record <-- Not here


Can anyone help me?

 

[BSman]

By next available password, I assume you mean the next one that would be in the list if the list were sorted by the password. If that is what you want, then do something like this:

1. Create a query on the table that has the two columns and sorts by the password column (ascending).

2. To find the next available password, use a statement something like this:

strNextPassword = DMin(&quot;[Password]&quot;,&quot;[tblAust.Organisation]&quot;,&quot;[Organisation] IS Null&quot

This is the password you will assign. Then you need to write an update statement that will update the Organisation column to the organization name where the password = strNextPassword so that the selected password will not be available next time.

Bob