Same form for new/edit record problem

Hi there,

I have a form which has a load event to get the dmax(UserID)+5 to populate
the UserID with the new code. However, I want to use this same form to edit
existing records. When u user double-clicks on another sub-form's UserID
field, it should open the same "edit users" form as stated first, but this
time obviously with the selected record's information. The problem is that
the form's load event gets the new code, and therefore don't allow for
editing of records. How can I do both with the same form?

Thank you kindly!

--
The Psyber Fox
http://www.psyberconsulting.co.za

Move the code from the form's Load event into its BeforeInsert event.

The text box will populate when the user starts entering data at a new
record.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"PsyberFox" wrote in message
...
Hi there,

I have a form which has a load event to get the dmax(UserID)+5 to populate
the UserID with the new code. However, I want to use this same form to
edit
existing records. When u user double-clicks on another sub-form's UserID
field, it should open the same "edit users" form as stated first, but this
time obviously with the selected record's information. The problem is that
the form's load event gets the new code, and therefore don't allow for
editing of records. How can I do both with the same form?

Hi and thank you for the reply.

I'm still not getting it right. Herewith some code that i'm trying to run:

This one is on the edit-form after the user selected New from another
sub-form:
Private Sub Form_BeforeInsert(Cancel As Integer)
Dim GetUserID As String
Dim NextUserID As String
GetUserID = DMax("UserID", "tbl_XUsers")
NextUserID = "U" & Format(Val(Mid(GetUserID, 2)) + 5, "0000")
[UserID].Value = NextUserID
UserName.SetFocus
End Sub

This one is the one that runs when the user has double-clicked the userID on
the aforementioned sub-form:
Private Sub UserID_DblClick(Cancel As Integer)
DoCmd.Close
DoCmd.OpenForm "frm_Users_Edit", acNormal, , "UserID=" & UserID
End Sub

After the changes you have suggested, it now comes up with a blank UserID
whether I'm selecting new / edit...


--
The Psyber Fox
http://www.psyberconsulting.co.za


"Allen Browne" wrote:

Move the code from the form's Load event into its BeforeInsert event.

The text box will populate when the user starts entering data at a new
record.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"PsyberFox" wrote in message
...
Hi there,

I have a form which has a load event to get the dmax(UserID)+5 to populate
the UserID with the new code. However, I want to use this same form to
edit
existing records. When u user double-clicks on another sub-form's UserID
field, it should open the same "edit users" form as stated first, but this
time obviously with the selected record's information. The problem is that
the form's load event gets the new code, and therefore don't allow for
editing of records. How can I do both with the same form?

.

I did come right, thank you though!
W
--
The Psyber Fox
http://www.psyberconsulting.co.za


"Allen Browne" wrote:

Move the code from the form's Load event into its BeforeInsert event.

The text box will populate when the user starts entering data at a new
record.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"PsyberFox" wrote in message
...
Hi there,

I have a form which has a load event to get the dmax(UserID)+5 to populate
the UserID with the new code. However, I want to use this same form to
edit
existing records. When u user double-clicks on another sub-form's UserID
field, it should open the same "edit users" form as stated first, but this
time obviously with the selected record's information. The problem is that
the form's load event gets the new code, and therefore don't allow for
editing of records. How can I do both with the same form?

.

This message is dated earlier than the one that says you have it sorted, but
showed up later.

Can you confirm if you still need help with this?

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"PsyberFox" wrote in message
...
Hi and thank you for the reply.

I'm still not getting it right. Herewith some code that i'm trying to run:

This one is on the edit-form after the user selected New from another
sub-form:
Private Sub Form_BeforeInsert(Cancel As Integer)
Dim GetUserID As String
Dim NextUserID As String
GetUserID = DMax("UserID", "tbl_XUsers")
NextUserID = "U" & Format(Val(Mid(GetUserID, 2)) + 5, "0000")
[UserID].Value = NextUserID
UserName.SetFocus
End Sub

This one is the one that runs when the user has double-clicked the userID
on
the aforementioned sub-form:
Private Sub UserID_DblClick(Cancel As Integer)
DoCmd.Close
DoCmd.OpenForm "frm_Users_Edit", acNormal, , "UserID=" & UserID
End Sub

After the changes you have suggested, it now comes up with a blank UserID
whether I'm selecting new / edit...


--
The Psyber Fox
http://www.psyberconsulting.co.za


"Allen Browne" wrote:

Move the code from the form's Load event into its BeforeInsert event.

The text box will populate when the user starts entering data at a new
record.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"PsyberFox" wrote in message
...
Hi there,

I have a form which has a load event to get the dmax(UserID)+5 to
populate
the UserID with the new code. However, I want to use this same form to
edit
existing records. When u user double-clicks on another sub-form's
UserID
field, it should open the same "edit users" form as stated first, but
this
time obviously with the selected record's information. The problem is
that
the form's load event gets the new code, and therefore don't allow for
editing of records. How can I do both with the same form?

.