adding a record then opening a form bound to it

  • Thread starter Thread starter MattE
  • Start date Start date
M

MattE

I may be missing something easy, but I'm using a ComboBox to allow the
user to select a record. If the record's not there, they click a "New"
button. I want this button to add a new record and open another form
which allow them to edit the record.

I'm currently using the AutoNumbered record id in the OpenForm criteria
to determine which record to bind to the form:

Set ComboBox = Me.Controls("ComboMaterial") ' this value will be the
record id (AutoNumber)
Set rs = ComboBox.Recordset

' open the edit form using the AutoNumbered record id as the criteria
DoCmd.OpenForm "Material", , , "MaterialId = " & Str$(ComboBox.Value)

But if (before the OpenForm) I do rs.AddNew, then how do I bind the
edit form with the new record using the new AutoNumbered record id?

Am I on the right track? Hope this is clear, thanks!
 
This isn't good practice creating a record with no data in and then opening
another form to actually enter the data, you could end up with *a lot* of
useless table entries, best way would be to open the form in dataentry mode
and let the user enter all the data in 1 go and then save it at the end.

TonyT..
 
Hello Matt,

I agree with Tony that this may cause some blank records if users decide
not to add the records actually. However, if this is important to your
business and you may consider use the following method to get the newid of
the inserted row:

Private Sub Command2_Click()
Dim db As Database
Dim newid As Integer
Dim rs As Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("select * from autoid", dbOpenDynaset)
rs.AddNew
rs.Update
rs.Requery
rs.MoveLast
newid = rs("myid")
DoCmd.OpenForm "autoid", , , "Myid =" & newid
rs.Close
Set rs = Nothing

End Sub

I used a table autoid and myid is a autonumber field. This seems work fine
on my side.

If you have further questions or concerns on the issue, please feel free to
let's know. Thank you!

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
 
Back
Top