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! 1 1589
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! This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: vgrssrtrs |
last post by:
<html>
<head>
<script language="JavaScript">
<!--
/*
*** Multiple dynamic combo boxes
*** by Mirko Elviro, 9 Mar 2005
***
***Please do not remove this comment
|
by: Heather |
last post by:
Hi
I am desparately looking for advice in relation to storing the results
after selecting items from two combo boxes on a Referral form. The
first combo box 'ctl Type' displays a full list of...
|
by: jquest |
last post by:
Hi Again;
I have had help from this group before and want to thank everyone,
especially PCDatasheet.
My database includes a field called HomePhone, it uses the (xxx)xxx-xxx
format to include...
|
by: Regnab |
last post by:
I'm making a database to record training that employees have recieved.
For each training excercise, they recieve a mark between 1 and 3.
Initially, when there weren't so many training elements, I...
|
by: Jeff Mason |
last post by:
I'm observing some strange behavior when I use a bound combo box in conjunction with
the combo's anchor property. I define a form which contains just a textbox and a
combo box. The text box is...
|
by: hmiller |
last post by:
Hey everyone,
I am having a hell of a time trying to set this menu system up. Here's
what I'm trying to do.
Combo Box One; is populated by names under properties "row source"
"Phase 1"...
|
by: kiranmn75 |
last post by:
I want to dynamically populate a combo box through javascript. Data is
coming from a array. Sometimes data list may contain items in excess of
2000.
Explorer takes more than 5 seconds to...
|
by: Dave |
last post by:
I want to put the information that the user selects in my combo boxes
into a subform that lies on the same form as the combo boxes.
Thanks for your help already,
Dave
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
| |