Re: allow new data to be saved/shown in combo boxes

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: auntyjack (anonymous_at_discussions.microsoft.com)
Date: 06/26/04 

Date: Sat, 26 Jun 2004 06:59:48 -0700

Still not successful - I understand combo box name (it is
also ModelNo - I changed it) and know where to write the
code, but not good on code.
I'm not even getting the message that the name is not on
the list when I try which is the first part of the code.
I have Model Names in a table of thier own from which the
combo box gets info and stores it to the the customers
record. Are there any other settings I should set that may
have an influence on it - I have got - Limit to list - NO.
mostly use Wizards but can do a bit by myself.
Also can you provide info on how to update the list in the
current form when you go directly to the form/table with
the combo box info - add more names, then go back the the
current form without having to close it and come back to
make the new ones appear on the list - I currenly update
the info by pressing F9 in the field but want something
better for others to use. Your help is very much
appreciated as I live in a faily isolated area and trying
to write a database for work to record info on customers
plant/equipment for others to use. Work is also in a small
country town so a bit hard to find help and others who
know how to do these things.
Thanks
>-----Original Message-----
>Hi
>
>CbxAEName should be the name of your combo box (so change
that if
>necessary - if you right mouse click on the combo box and
choose properties
>& then choose "not in list" and then click on the ...
then choose code
>builder it will put the sub & end sub lines in for you
then
>if the table is called ModelNo and the field is Model
Number then you'll
>need to modify your code as follows:
>
> Dim db As DAO.Database
> Dim rs As DAO.Recordset
> Dim strMsg As String
>
> strMsg = NewData & " is not an available Model
number " & vbCrLf &
>vbCrLf
> strMsg = strMsg & "Do you want to associate the new
Name to the current
>Model Number List?"
> strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to
link or No to re-type
>it."
>
> If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new
model number?") = vbNo
>Then
> Response = acDataErrContinue
> Else
> Set db = CurrentDb
> Set rs = db.OpenRecordset("ModelNo",
dbOpenDynaset)
> On Error Resume Next
> rs.AddNew
> rs![model number] = NewData
> rs.Update
>
> If Err Then
> MsgBox "An error occurred. Please try again."
> Response = acDataErrContinue
> Else
> Response = acDataErrAdded
> End If
>
> End If
>
>rs.Close
>Set rs = Nothing
>Set db = Nothing
>
> End Sub
>---
>let me know how you go
>
>Cheers
>JulieD
>
>
>
>"which code refers to table names/record names"
><anonymous@discussions.microsoft.com> wrote in message
>news:215b101c45a98$21d5e010$a501280a@phx.gbl...
>> I've tried the code on the web site but still can't get
>> it - my table is "ModelNo" and field name "model
number"
>> I've tried chaging areas of the code to make it add the
>> data to the table and show in the list but not
successful.
>> This is what I have (don't know what cbxAEName refers
to):
>> Private Sub cbxAENAME_NotInList(NewData As String,
>> Response As Integer)
>> Dim db As DAO.Database
>> Dim rs As DAO.Recordset
>> Dim strMsg As String
>>
>>
>> strMsg = "'" & NewData & "' is not an available Model
>> Number " & vbCrLf & vbCrLf
>> strMsg = strMsg & "Do you want to associate the new
>> Name to the current DLSAF?"
>> strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to
link
>> or No to re-type it."
>>
>> If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new
>> name?") = vbNo Then
>> Response = acDataErrContinue
>> Else
>> Set db = CurrentDb
>> Set rs = db.OpenRecordset("tblModelNO",
>> dbOpenDynaset)
>> On Error Resume Next
>> rs.addnew
>> rs!modelnumber = NewData
>> rs.Update
>>
>> If Err Then
>> MsgBox "An error occurred. Please try
again."
>> Response = acDataErrContinue
>> Else
>> Response = acDataErrAdded
>> End If
>>
>> End If
>>
>> rs.close
>> Set rs = Nothing
>> Set db = Nothing
>> End Sub
>> >-----Original Message-----
>> >Hi
>> >
>> >you might like to have a look at
>> >http://www.mvps.org/access/forms/frm0015.htm
>> >"Add item to combo box using OnNotinList event"
>> >
>> >cheers
>> >JulieD
>> >
>> >"auntyjack" <anonymous@discussions.microsoft.com> wrote
>> in message
>> >news:20e4c01c459ee$8e3038f0$a501280a@phx.gbl...
>> >> I would like the text to allow data to be added to
combo
>> >> boxes as you go without having to go out and add the
>> text
>> >> to the underlying table - I have it set for 'limit
it to
>> >> list' NO. I want the new data to show in the drop
down
>> >> lists.there will be a lot of new items
>> >> I know this is not always good but more suitable for
my
>> >> database.
>> >> Also the text for and correct location (eg After
Update)
>> >> to ensure that new items added to the underlying
forms
>> >> will show in the combo boxes when you need to go out
of
>> >> the form to add missing items in combo boxes. there
are
>> >> not many new items
>> >> These are 2 different situations
>> >
>> >
>> >.
>> >
>
>
>.
>

Relevant Pages

  • Re: FTP From Excel
    ... including whether a transfer was successful, or if a file wasn't found. ... Andy Wiggins ... >> Sub PublishFile() ... >> Dim strDirectoryList As String ...
    (microsoft.public.excel.programming)
  • Project Error
    ... Private Declare Sub Sleep Lib "Kernel32" ... Dim strDataSrc As String ...
    (microsoft.public.vb.bugs)
  • Re: How Much Longer Can SRians Ignore Their Fundamental Error.
    ... ecc As Variant ... Dim lightspeed, position, circlerun, Pref, prefmod As Integer, ... Private Sub Combo12_Change ... Erase Vangle ...
    (sci.astro)
  • Re: How Much Longer Can SRians Ignore Their Fundamental Error.
    ... ecc As Variant ... Dim lightspeed, position, circlerun, Pref, prefmod As Integer, ... Private Sub Combo12_Change ... Erase Vangle ...
    (sci.physics.relativity)
  • Re: Rearrange desktop
    ... Dim m_htTitlebar ' used in geom calcs ... Const GWL_STYLE = ... Sub RepositionDesktopIcons() ... Public Property Let Mask ...
    (microsoft.public.scripting.vbscript)