473,326 Members | 2,134 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Add new record via form where data entered is NotInList

18
I am using Access 2003. My Form has 4 fields:
MailingListID, auto generated – primary key
LastName
FirstName
NickName

I used the Combo box wizard to set up look-up box on the form. I answered the wizards question by saying “find a record in the form”, I choose the first 3 fields for the combo box and made field 1 width = 0. The wizard then created the unbound control box and generated the code shown below

Private Sub Combo14_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[MailingListID] = " & Str(Nz(Me![Combo14], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

This seems to work fine. I can pull down on the list and click on the name I want and info for that record shows up in form or if I enter a last name not on the pull down list, I get the usual error message for not in list.

I thought it would be simple to modify Combo14 to simply add a new record if it didn’t find the LastName I typed in. So I modified Combo14 as you see below. But I still get the not in list error message when I try it - instead of the form for the new record – which should have the next autonumber in the MailingListID field and “Doe” in the LastName field. [Actually in real life I would capture the LastName the user typed and put that in instead of Doe but I’m just trying to get this to work.]

Private Sub Combo14_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[MailingListID] = " & Str(Nz(Me![Combo14], 0))
If Not rs.EOF Then
Me.Bookmark = rs.Bookmark
Else
rs.AddNew
rs!LastName = "Doe"
rs.Update
Me.Bookmark = rs.Bookmark
End If

End Sub

What should the procedure look like to do what I want?

PostScript: I also tried leaving Combo_14 as the wizard created and putting a macro GoToRecord (new) in the NotInList event field. But this caused Access to abruptly shut down.

Thanks for any help. J Hite
Nov 8 '06 #1
1 3662
MMcCarthy
14,534 Expert Mod 8TB
You cannot add value by typing into combo box. Type last name in an unbound text box. Then check this out ...

Expand|Select|Wrap|Line Numbers
  1.  
  2. ' change to after update event on new textbox 
  3. Private Sub textboxname_AfterUpdate()
  4. ' Find the record that matches the control.
  5. Dim rs As Recordset ' declare as recordset rather than object
  6.  
  7. ' this assumes that form is bound to relevant table or query and fields are bound on the form 
  8. Set rs = Me.RecordsetClone ' no dot between recordset and clone
  9. rs.FindFirst "[LastName]='" & Me.[textboxname] & "'" ' lookup lastname in records on form
  10.  
  11. If rs.NoMatch Then ' if name not found then
  12. rs.AddNew ' add new record
  13. rs!LastName = Me.[textboxname] ' set LastName to value typed in
  14. rs.Update
  15. End If
  16.  
  17. ' always reset recordset
  18. rs.Close
  19. Set rs = Nothing
  20.  
  21. Me.Combo14.Requery ' requery the combo box to show new record
  22.  
  23. End Sub
  24.  
  25.  
Nov 9 '06 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

6
by: Steve | last post by:
I have a form, primary subform and secondary subform. A tab control takes up all the area of the primary subform. There are about 15 tabs on the tabcontrol. Each tab contains fields from the same...
6
by: 6thirty | last post by:
Hi, I've created a stocktaking database using Access XP. This is indexed by two fields - part number and shelf location. I am currently inputting all the data via a form. When I have entered a...
3
by: Tom | last post by:
Say a form has a subform, a button that pops up a message box and a cancel button. Q1. If data is entered in one or more fields on the main form and the user then clicks the button that opens the...
5
by: Ross A. Finlayson | last post by:
Hi, I'm scratching together an Access database. The development box is Office 95, the deployment box Office 2003. So anyways I am griping about forms and global variables. Say for example...
6
by: kaosyeti | last post by:
hey.... i have a duplicate record issue i could use some help with. on a form that has 2 unbound controls, txtboxyear and cboxmonth, together will automatically fill in an invisible txtboxdate...
10
by: robingSA | last post by:
Hi...I have a number of Combo Boxes on a form. If one types a name which is not in the table, I want it to add the typed, new item. I am using the following code to do this and it works well,...
5
AccessIdiot
by: AccessIdiot | last post by:
This is kind of an odd request. I have a form with a combo box. If an item isn't in the combo box you can add it by launching a new form (using NotInList). I would like for the main form to go...
18
by: Drayno241 | last post by:
I'm working in access 2002. I have three tables : 1- District Data (Student ID, name, grade, etc) 2- Rosters (RRec ID,Campus, Teacher ID) 3- Students on Roster(SRec ID, RRec ID, Student ID) ...
6
by: Volker Neurath | last post by:
Hi all, I have a Problem with combobox-property "NotInList" and an unbound Form. The situation: On my main form i have three comboboxes for data-exchange (here: Names of distributor,...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
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...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
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...
0
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
0
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...
0
isladogs
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...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.