Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
laasunde
Starting Member
3 Posts |
 Posted - 2003-02-25 : 03:30:04
|
| Morning people.I've got a simple database with three tables.1. User with ID_User(autonumber) and Name(text)2. Link with ID_User(number) and ID_Job(number)3. Job with ID_Job(autonumber) and Type(text)There is a one-to-many relationship between User and Link and between job and Link.I've made a form in Access2002 with one textbox for the User's name and one dropdownbox with all the possible Job types.Inserting the new user into the table User is no problem :Insert into User(Name) Values('John Smith')I also need to update the Link table so that the new user is linked with the correct job. Is there any way in Access to retrive the last used autonumber in a table ? I was thinking about doing :"select ID_User, Name from User where Name = 'John Smith'But that could return more than one John Smith and I might update the wrong person.Appreciate any help. |
|
|
JimL
SQL Slinging Yak Ranger
1537 Posts |
Posted - 2003-02-25 : 08:15:47
|
| The simplest way to have them linked is to put the job number in a pull down field in your form, you could also us a subform to create the relationship. If all you want to do is access the last entry create a View that has the autonumber field in it, sort decending and select the top row like this CREATE VIEW dbo.<VIEWNAME>ASSELECT TOP 1 <FIELDNAME>FROM dbo.<TABLENAME>ORDER BY <FIELDNAME> DESC |
 |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-02-25 : 08:27:25
|
| You could SELECT MAX(User_ID) after you add it, or use the DMAX() function in Access VBA.You could also add the link table into a subform on that main form, and link it to the main form by User_ID. then, but just inserting records into that subform, they will have the same user_Id as the parent.- Jeff |
|
|
|
|
|
|
Insert