Re: Generate next primary key value

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Thanks Brian, this looks like just the ticket. The multi-user issues won't
be a problem because the forms will used by only a couple of people.
cheers
peterD


"Brian" <Brian@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:10835F4F-7981-4B2F-B767-208AC6A2DB7D@xxxxxxxxxxxxxxxx
> In VBA
>
> Where KeyID is the name of a text box or other control bound to the
primary
> key of the table:
>
> KeyID = DMax("[KeyID]","[TableName]") +1
>
> (Assuming you want to increment by 1).
>
> As you mentioned, be careful where you place this statement. What if one
> user opens the form while another user is creating a new record but has
not
> yet saved it? Whoever saves first will get to keep the ID. The
BeforeInsert
> event fires when the user types the first character, not when the record
is
> saved.
>
> If the ID is purely a key and is invisible to the user, you can just
create
> it in Form_BeforeUpdate for new records.
>
> Private Sub Form_BeforeUpdate(Cancel As Integer)
> If Form.NewRecord then KeyID = DMax("[KeyID]","[TableName]") +1
> End Sub
>
> If the user needs to see the key, then put a MsgBox in Form_AfterInsert
that
> shows the number to the user after the save.
>
> "peterDavey" wrote:
>
> > G'day all,
> > I'm pretty new to Access (but have a fair amount of Excel VBA
experience) so
> > I suspect this will be the first of many posts.
> >
> > I'm creating an Access 2000 front end for a SQL Server database using
linked
> > tables. Most of my forms will be used to maintain reference tables.
The
> > problem I'm having is that when I create a new record (e.g. to add a new
> > department in the department table), I need to run a query to find the
> > maximum current value in the primary key column so I can use the next
value
> > for the ID of the new record. I can't use the Access AutoNumber data
type
> > because it isn't supported by SQL Server.
> >
> > I know the SQL I need to run and I assume that it will need to be run by
the
> > Form_BeforeInsert event/sub but I'm just not sure how I run the SQL to
get
> > the value from within this sub.
> >
> > I'll be very grateful for any assistance.
> >
> > cheers
> > peterDavey
> > Melbourne
> >
> >
> >


.

Relevant Pages

  • FYI: Delete still shows the deleted record
    ... "kit" wrote: ... new SQL Server DB and I'm borrowing code from the previous SQL Server DB ... Put this in the click event of your "DELETE" command button: ... Public Sub DelCurrentRec ...
    (microsoft.public.access.forms)
  • ADO in HTA script fails in Windows PE
    ... In our company we are developing a Windows installation ... connects to a SQL server and fetches the possible options ... Sub Window_Onload ... set objDBConnection = Wscript.CreateObject ...
    (microsoft.public.windowsxp.setup_deployment)
  • Re: Access 2003 ADP to SQL 2000
    ... > I have used Access with SQL Server for over 8 years. ... > Dim dbs As Database, rs As Recordset, tdfAccess As TableDef ... > Exit Sub ... > Set dbs = CurrentDb ...
    (microsoft.public.access.externaldata)
  • Passing variant array into a VSTO add-in
    ... 100 templates that contain macros that access a SQL server. ... I am able to retrieve data from the SQL server into a VSTO ... Public Sub ProcWM_GetAppealJudgeAs Variant, ... Public Sub GetData ...
    (microsoft.public.vsnet.vstools.office)
  • Re: DSN less link to SQL
    ... I use this procedure to re-create links to SQL Server. ... Public Sub LinkSQLServerTables ... Dim dbs As Database, rs As Recordset, tdfAccess As TableDef ... Set dbs = CurrentDb ...
    (microsoft.public.access.externaldata)