By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,027 Members | 1,261 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,027 IT Pros & Developers. It's quick & easy.

A Better Combo Box??

P: n/a
AW
Hi all,

I wrote some code that would enable a combo box to find a specific record,
and if unsuccessful, to create a new one. Since the uderlying table does
not use the Autonumber to generate a key, the combo box assigns this also.

Q: Is there a better way to do this?

___________________________________________

Private Sub ComboBox_AfterUpdate()
On Error GoTo err_ComboBox
Dim strsql As String
Dim db As Database, cnst As Recordset
Dim CaseNum As Long
Me.RecordsetClone.FindFirst "[ID_no] = '" & Me![ComboBox] & "'"
Me.Bookmark = Me.RecordsetClone.Bookmark
If [ID_no] <> Me![ComboBox] Then
DoCmd.GoToRecord , , acNewRec
Set db = CurrentDb()
strsql = "SELECT DISTINCTROW Max(ClientTable.KeyNumber) AS
KeyNumber FROM ClientTable"
Set cnst = db.OpenRecordset(strsql)
CaseNum = cnst!Keynumber + 1
cnst.Close
Me!Keynumber = CaseNum
ID_no = Me!ComboBox
db.Close
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
Else
CategoryBox.SetFocus
End If
DateBox.SetFocus
'Exit_ComboBox_AfterUpdate:
' Exit Sub
err_ComboBox:
If Err.Number = "3021" Then
DoCmd.GoToRecord , , acNewRec
[ID_no] = Me![ComboBox]
Set db = CurrentDb()
strsql = "SELECT DISTINCTROW Max(ClientTable.KeyNumber) AS
KeyNumber FROM ClientTable"
Set cnst = db.OpenRecordset(strsql)
CaseNum = cnst!keynumber + 1
cnst.Close
Me!keynumber = CaseNum
db.Close
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
End If
'Resume Exit_ComboBox_AfterUpdate
End Sub
_________________________________________________
--
Thanks a million!
Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Take a look at the NotInList event. There's a sample at
http://www.mvps.org/access/forms/frm0015.htm at "The Access Web"

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)
"AW" <aw@nospam.net> wrote in message
news:bc******************************@news.1usenet .com...
Hi all,

I wrote some code that would enable a combo box to find a specific record,
and if unsuccessful, to create a new one. Since the uderlying table does
not use the Autonumber to generate a key, the combo box assigns this also.

Q: Is there a better way to do this?

___________________________________________

Private Sub ComboBox_AfterUpdate()
On Error GoTo err_ComboBox
Dim strsql As String
Dim db As Database, cnst As Recordset
Dim CaseNum As Long
Me.RecordsetClone.FindFirst "[ID_no] = '" & Me![ComboBox] & "'"
Me.Bookmark = Me.RecordsetClone.Bookmark
If [ID_no] <> Me![ComboBox] Then
DoCmd.GoToRecord , , acNewRec
Set db = CurrentDb()
strsql = "SELECT DISTINCTROW Max(ClientTable.KeyNumber) AS
KeyNumber FROM ClientTable"
Set cnst = db.OpenRecordset(strsql)
CaseNum = cnst!Keynumber + 1
cnst.Close
Me!Keynumber = CaseNum
ID_no = Me!ComboBox
db.Close
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
Else
CategoryBox.SetFocus
End If
DateBox.SetFocus
'Exit_ComboBox_AfterUpdate:
' Exit Sub
err_ComboBox:
If Err.Number = "3021" Then
DoCmd.GoToRecord , , acNewRec
[ID_no] = Me![ComboBox]
Set db = CurrentDb()
strsql = "SELECT DISTINCTROW Max(ClientTable.KeyNumber) AS
KeyNumber FROM ClientTable"
Set cnst = db.OpenRecordset(strsql)
CaseNum = cnst!keynumber + 1
cnst.Close
Me!keynumber = CaseNum
db.Close
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
End If
'Resume Exit_ComboBox_AfterUpdate
End Sub
_________________________________________________
--
Thanks a million!

Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.