Add new record using first available non-numeric ID; update two ta

[Allison]

Access 2003, Windows XP Pro SP2

I have two tables, tblDed and tblVendor.

tblDed contains two columns (DedID and Avail).

The first column contains a list of all DedIDs allowed to be used. The
second is a Yes/No choice indicating whether or not that DedID has been used
already.

tblVendor contains multiple columns with misc. data relevant to the Vendor.

I want to create a form that will accomplish three things:

1. Use the next available unused DedID when a command button on a referring
menu page (frmMenu, btnNew) is pressed.
2. Mark the now-used DedID in tblDed as "No" - no longer available
3. Upon completion of the form, save the results to tblVendor using btnSave

I'm stumped on Numbers 1 and 2.

Would somebody give me some pointers for Numbers 1 and 2? Are they even
possible? Thank you for your time.

 

[Klatuu]

In the new form you want to open, this code will find the first available
DedID and update it a Not available. I would suggest it be placed in the
Form Load event.
Put the Dim for strDedID at the top of the form module so it is visible
anywhere in your form module code:

Private Sub Form_Load()
Dim rstDed As Recordset

Set rstDed = Currentdb.OpenRecordset("SELECT TOP 1 DedID, Available FROM
tblDed WHERE Available =True ORDER BY DedID;")
With rstDed
strDedID = ![DedID]
.Edit
![Available] = False
.Update
.Close
End With
Set rstDed = Nothing
End Sub

 

[Allison]

Thank you for your help. I appreciate the assistance.

In the new form you want to open, this code will find the first available
DedID and update it a Not available. I would suggest it be placed in the
Form Load event.
Put the Dim for strDedID at the top of the form module so it is visible
anywhere in your form module code:

Private Sub Form_Load()
Dim rstDed As Recordset

Set rstDed = Currentdb.OpenRecordset("SELECT TOP 1 DedID, Available FROM
tblDed WHERE Available =True ORDER BY DedID;")
With rstDed
strDedID = ![DedID]
.Edit
![Available] = False
.Update
.Close
End With
Set rstDed = Nothing
End Sub
--
Dave Hargis, Microsoft Access MVP

Access 2003, Windows XP Pro SP2

I have two tables, tblDed and tblVendor.

tblDed contains two columns (DedID and Avail).

The first column contains a list of all DedIDs allowed to be used. The
second is a Yes/No choice indicating whether or not that DedID has been used
already.

tblVendor contains multiple columns with misc. data relevant to the Vendor.

I want to create a form that will accomplish three things:

1. Use the next available unused DedID when a command button on a referring
menu page (frmMenu, btnNew) is pressed.
2. Mark the now-used DedID in tblDed as "No" - no longer available
3. Upon completion of the form, save the results to tblVendor using btnSave

I'm stumped on Numbers 1 and 2.

Would somebody give me some pointers for Numbers 1 and 2? Are they even
possible? Thank you for your time.