Pulling data from a linked SQL table

[John]

Hello All,

I have a SQL table that I have linked in my database which has a field
called ID. What I am trying to do is when I open my data entry form I
want to pull the last ID number plus 1 and put that number in my field
on my form called ID.

I can't seem to get it to pull the data from the table and need help
with the correct syntax.

Any help is appreciated.

Thanks,
~John

 

[John W. Vinson]

Hello All,

I have a SQL table that I have linked in my database which has a field
called ID. What I am trying to do is when I open my data entry form I
want to pull the last ID number plus 1 and put that number in my field
on my form called ID.

I can't seem to get it to pull the data from the table and need help
with the correct syntax.

Any help is appreciated.

Thanks,
~John

Try using the Form's Load event:

Private Sub Form_Load()
Me!ID.DefaultValue = NZ(DMax("[ID]", "[YourLinkedTableName]")) + 1
End Sub

This will set the DEFAULT for new records, which is probably preferable to
creating a record in the Load event, as that record might end up not getting
edited; it would then be a pretty much worthless empty record cluttering your
table.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[John]

Hello All,

I have a SQL table that I have linked in my database which has a field
called ID.  What I am trying to do is when I open my data entry form I
want to pull the last ID number plus 1 and put that number in my field
on my form called ID.

I can't seem to get it to pull the data from the table and need help
with the correct syntax.

Any help is appreciated.

Thanks,
~John

Try using the Form's Load event:

Private Sub Form_Load()
Me!ID.DefaultValue = NZ(DMax("[ID]", "[YourLinkedTableName]")) + 1
End Sub

This will set the DEFAULT for new records, which is probably preferable to
creating a record in the Load event, as that record might end up not getting
edited; it would then be a pretty much worthless empty record cluttering your
table.
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com

Thank you very much John, this works exactly how I want it to.

I appreciate your help.

~John