Automatically detect an Auto number

  • Thread starter Thread starter Joe
  • Start date Start date
J

Joe

On the form ClientInvesment there is an Add client button
that opens a different form. When you click Save, the new
client is being saved with an Auto Number.
On the form ClientInvesment I also have a Search button
and after updating it, it loads some of the client's
personal information and all investments for that client
ID.

Remember! Client ID is an Auto number.

How can I make the Search button should automatically
detect the new Auto number and fill the client's
information onto the ClientInvesment form?

Is there anyway, that I can capture an Auto number while
its being created?
Is there any code to put in this procedure?

Your help is appreciated.
Please let me know if you need additional clarifications.

Thanks

Joe
=========
Private Sub SaveMember(lngClientID)
Dim rs As ADODB.Recordset
Set cnn = CurrentProject.Connection
Set rs = New ADODB.Recordset
rs.Open "TblClients", cnn, adOpenStatic, adLockOptimistic
If lngClientID = 0 Then
rs.AddNew
Else
rs.Find "ClientID = " & lngClientID
If rs.EOF Then
Beep
MsgBox "can't find this record"
GoTo Exit_SaveMember
End If
End If

With rs
!FirstName = FirstName
!LastName = LastName
!Zip = Zip
..Update
End With
===============
 
You can't make the search button find a new record since it won't know what
a new record is unless you have a field to keep track of this. The best
thing would be to put code in the Save button of the form where the client
is created to copy the ClientID back to the calling form. Then you can run
your search routine.

Kelvin
 
Comments interspersed.
On the form ClientInvesment there is an Add client button
that opens a different form. When you click Save, the new
client is being saved with an Auto Number.
On the form ClientInvesment I also have a Search button
and after updating it, it loads some of the client's
personal information and all investments for that client
ID.

Remember! Client ID is an Auto number.

How can I make the Search button should automatically
detect the new Auto number and fill the client's
information onto the ClientInvesment form?
Click to expand...

The form's recordset must be requeried to include added records.
Is there anyway, that I can capture an Auto number while
its being created?
Click to expand...

As soon as you have begun a new record, the autonumber value is available.
Simply retrieve it after invoking the ".AddNew" method.
Is there any code to put in this procedure?
Click to expand...

Well, I'm not well versed in ADO, but your code appears to be okay, except for
the fact that you didn't Dim your variable "cnn" and you need to add a line to
retrieve the autonumber value, which would be after the line ".AddNew".

Does this give you enough to work with?
 
How can I make the Search button should automatically
Are you using an unbound form? My answer may have been just a little inaccurate
if you are. <g>
 
Back
Top