CombBox to SEARCH and ADD records.

Greetings,

To clear up my previous post (much confusement in them!) I have a form where 
all navigation buttons have been created manually (hidden navigation bar).

I want a combo box to allow user to both SEARCH and ADD new values.

I can SEARCH with a combo box after using the button wizard (search records 
option) but cannot change its properties to add new values to the list.

Can this be done with form/vba code?

Thanks
Rob


0
Rob
2/23/2008 1:19:06 PM
access 16762 articles. 3 followers. Follow

7 Replies
712 Views

Similar Articles

[PageSpeed] 8

Rob W wrote:
> Greetings,
>
> To clear up my previous post (much confusement in them!) I have a
> form where all navigation buttons have been created manually (hidden
> navigation bar).
> I want a combo box to allow user to both SEARCH and ADD new values.
>
> I can SEARCH with a combo box after using the button wizard (search
> records option) but cannot change its properties to add new values to
> the list.
> Can this be done with form/vba code?

You can alter the RowSource of the ComboBox to make it a UNION query.  The 
second part of the UNION can add the words "Add New" and you can make it display 
in the first row.  Then in the AfterUpdate event code that already exists on 
your ComboBox you can test for the value "Add New" and have that take the form 
to the new reocrd position.

-- 
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt   at   Hunter   dot   com 


0
Rick
2/23/2008 1:50:05 PM
Wow. Thanks a simple, yet clever idea.

However I need some guide with my union statement :-

SELECT tblModule.ModuleId,tblModule.ModuleName, tblModule.Archive
FROM tblModule
UNION
SELECT tblModule.ModuleId, ModuleName as "Add New Record", tblModule.Archive
FROM tblModule;

It displays the modules names (columns widtsh 0;5;0 though bound to 
module.ID) and not "Addd new Record"

I guess I should be concearned that there is macro code behind the 
AfterUpdate combo box event and it will run the macro and the vba code?

Cheers
Rob


"Rick Brandt" <rickbrandt2@hotmail.com> wrote in message 
news:owVvj.3467$Mh2.756@nlpi069.nbdc.sbc.com...
> Rob W wrote:
>> Greetings,
>>
>> To clear up my previous post (much confusement in them!) I have a
>> form where all navigation buttons have been created manually (hidden
>> navigation bar).
>> I want a combo box to allow user to both SEARCH and ADD new values.
>>
>> I can SEARCH with a combo box after using the button wizard (search
>> records option) but cannot change its properties to add new values to
>> the list.
>> Can this be done with form/vba code?
>
> You can alter the RowSource of the ComboBox to make it a UNION query.  The 
> second part of the UNION can add the words "Add New" and you can make it 
> display in the first row.  Then in the AfterUpdate event code that already 
> exists on your ComboBox you can test for the value "Add New" and have that 
> take the form to the new reocrd position.
>
> -- 
> Rick Brandt, Microsoft Access MVP
> Email (as appropriate) to...
> RBrandt   at   Hunter   dot   com
> 


0
Rob
2/23/2008 2:17:59 PM
Rob W wrote:
> Wow. Thanks a simple, yet clever idea.
>
> However I need some guide with my union statement :-
>
> SELECT tblModule.ModuleId,tblModule.ModuleName, tblModule.Archive
> FROM tblModule
> UNION
> SELECT tblModule.ModuleId, ModuleName as "Add New Record",
> tblModule.Archive FROM tblModule;

SELECT tblModule.ModuleId,tblModule.ModuleName, tblModule.Archive
FROM tblModule
UNION
SELECT -9999 as ModuleId, "Add New Record" as ModuleName, null as Archive
tblModule.Archive FROM tblModule;
ORDER BY ModuleId

> It displays the modules names (columns widtsh 0;5;0 though bound to
> module.ID) and not "Addd new Record"
>
> I guess I should be concearned that there is macro code behind the
> AfterUpdate combo box event and it will run the macro and the vba
> code?

Ugh.  I would replace the current macro with vba code that does both things.  Is 
this Access 2007?  Any other version and the wizard would have created VBA code 
for you in the first place.

-- 
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt   at   Hunter   dot   com



0
Rick
2/23/2008 4:41:15 PM
Its Access 2007.

Ive doen the combo box without the button wizard now, playign aroudn with 
this code:-

Private Sub cboModulename_AfterUpdate()
If cboModuleName.Value <> "NEW" Then

    ' Find the record that matches the control.

    Set rs = Me.Recordset.Clone

    rs.FindFirst "[ModuleId] = '" & Me![cboModuleName] & "'"

    If Not rs.EOF Then Me.Bookmark = rs.Bookmark

ElseIf cboModuleName.Value = "NEW" Then

        If rs.AbsloutePosition = rs.RecordCount - 1 Then

            DoCmd.GoToRecord , , acNewRec

        End If


End If

End Sub

I set the moduleId to be "NEW" in the union query. I s these ao reason for 
the -9999 value (I forgot to mention module ID is of type text - 8 chars)

Rob


"Rick Brandt" <rickbrandt2@hotmail.com> wrote in message 
news:P0Yvj.7508$Ru4.6659@newssvr19.news.prodigy.net...
> Rob W wrote:
>> Wow. Thanks a simple, yet clever idea.
>>
>> However I need some guide with my union statement :-
>>
>> SELECT tblModule.ModuleId,tblModule.ModuleName, tblModule.Archive
>> FROM tblModule
>> UNION
>> SELECT tblModule.ModuleId, ModuleName as "Add New Record",
>> tblModule.Archive FROM tblModule;
>
> SELECT tblModule.ModuleId,tblModule.ModuleName, tblModule.Archive
> FROM tblModule
> UNION
> SELECT -9999 as ModuleId, "Add New Record" as ModuleName, null as Archive
> tblModule.Archive FROM tblModule;
> ORDER BY ModuleId
>
>> It displays the modules names (columns widtsh 0;5;0 though bound to
>> module.ID) and not "Addd new Record"
>>
>> I guess I should be concearned that there is macro code behind the
>> AfterUpdate combo box event and it will run the macro and the vba
>> code?
>
> Ugh.  I would replace the current macro with vba code that does both 
> things.  Is this Access 2007?  Any other version and the wizard would have 
> created VBA code for you in the first place.
>
> -- 
> Rick Brandt, Microsoft Access MVP
> Email (as appropriate) to...
> RBrandt   at   Hunter   dot   com
>
>
> 


0
Rob
2/23/2008 4:51:40 PM
Rob W wrote:
> Its Access 2007.
>
> Ive doen the combo box without the button wizard now, playign aroudn
> with this code:-
>
> Private Sub cboModulename_AfterUpdate()
> If cboModuleName.Value <> "NEW" Then
>
>    ' Find the record that matches the control.
>
>    Set rs = Me.Recordset.Clone
>
>    rs.FindFirst "[ModuleId] = '" & Me![cboModuleName] & "'"
>
>    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
>
> ElseIf cboModuleName.Value = "NEW" Then
>
>        If rs.AbsloutePosition = rs.RecordCount - 1 Then
>
>            DoCmd.GoToRecord , , acNewRec
>
>        End If
>
>
> End If
>
> End Sub
>
> I set the moduleId to be "NEW" in the union query. I s these ao
> reason for the -9999 value (I forgot to mention module ID is of type
> text - 8 chars)

I dont understand why you are testing for the rs.AbsloutePosition in the ElseIf 
block.  If the user selects "Add New" then that is what they want regardless of 
where they are currently positioned (correct?).  I think you DO need to test to 
see if they are already at the New Record position in that spot though.

I used -9999 in combination with the ORDER BY so that Add New would always be at 
the top of the ComboBox list.

-- 
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt   at   Hunter   dot   com


0
Rick
2/23/2008 5:33:14 PM
Have I missed a parameter out? When i choose an existing record I get 
message

'Error message "Update or CancelUpdate without AddNew or Edit"

More testing to be done ...


"Rick Brandt" <rickbrandt2@hotmail.com> wrote in message 
news:zNYvj.3957$Mh2.1273@nlpi069.nbdc.sbc.com...
> Rob W wrote:
>> Its Access 2007.
>>
>> Ive doen the combo box without the button wizard now, playign aroudn
>> with this code:-
>>
>> Private Sub cboModulename_AfterUpdate()
>> If cboModuleName.Value <> "NEW" Then
>>
>>    ' Find the record that matches the control.
>>
>>    Set rs = Me.Recordset.Clone
>>
>>    rs.FindFirst "[ModuleId] = '" & Me![cboModuleName] & "'"
>>
>>    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
>>
>> ElseIf cboModuleName.Value = "NEW" Then
>>
>>        If rs.AbsloutePosition = rs.RecordCount - 1 Then
>>
>>            DoCmd.GoToRecord , , acNewRec
>>
>>        End If
>>
>>
>> End If
>>
>> End Sub
>>
>> I set the moduleId to be "NEW" in the union query. I s these ao
>> reason for the -9999 value (I forgot to mention module ID is of type
>> text - 8 chars)
>
> I dont understand why you are testing for the rs.AbsloutePosition in the 
> ElseIf block.  If the user selects "Add New" then that is what they want 
> regardless of where they are currently positioned (correct?).  I think you 
> DO need to test to see if they are already at the New Record position in 
> that spot though.
>
> I used -9999 in combination with the ORDER BY so that Add New would always 
> be at the top of the ComboBox list.
>
> -- 
> Rick Brandt, Microsoft Access MVP
> Email (as appropriate) to...
> RBrandt   at   Hunter   dot   com
>
> 


0
Rob
2/23/2008 6:08:37 PM
I've made a post on another access programming forum  which explains my 
problem, hopefully making it clearer as it contains code and images :-

http://www.access-programmers.co.uk/forums/showthread.php?t=144219

Thanks for all those who have helped me :-)

"Rob W" <Whllrob@aol.com> wrote in message 
news:us3g7ckdIHA.5348@TK2MSFTNGP03.phx.gbl...
> Have I missed a parameter out? When i choose an existing record I get 
> message
>
> 'Error message "Update or CancelUpdate without AddNew or Edit"
>
> More testing to be done ...
>
>
> "Rick Brandt" <rickbrandt2@hotmail.com> wrote in message 
> news:zNYvj.3957$Mh2.1273@nlpi069.nbdc.sbc.com...
>> Rob W wrote:
>>> Its Access 2007.
>>>
>>> Ive doen the combo box without the button wizard now, playign aroudn
>>> with this code:-
>>>
>>> Private Sub cboModulename_AfterUpdate()
>>> If cboModuleName.Value <> "NEW" Then
>>>
>>>    ' Find the record that matches the control.
>>>
>>>    Set rs = Me.Recordset.Clone
>>>
>>>    rs.FindFirst "[ModuleId] = '" & Me![cboModuleName] & "'"
>>>
>>>    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
>>>
>>> ElseIf cboModuleName.Value = "NEW" Then
>>>
>>>        If rs.AbsloutePosition = rs.RecordCount - 1 Then
>>>
>>>            DoCmd.GoToRecord , , acNewRec
>>>
>>>        End If
>>>
>>>
>>> End If
>>>
>>> End Sub
>>>
>>> I set the moduleId to be "NEW" in the union query. I s these ao
>>> reason for the -9999 value (I forgot to mention module ID is of type
>>> text - 8 chars)
>>
>> I dont understand why you are testing for the rs.AbsloutePosition in the 
>> ElseIf block.  If the user selects "Add New" then that is what they want 
>> regardless of where they are currently positioned (correct?).  I think 
>> you DO need to test to see if they are already at the New Record position 
>> in that spot though.
>>
>> I used -9999 in combination with the ORDER BY so that Add New would 
>> always be at the top of the ComboBox list.
>>
>> -- 
>> Rick Brandt, Microsoft Access MVP
>> Email (as appropriate) to...
>> RBrandt   at   Hunter   dot   com
>>
>>
>
> 


0
Rob
2/23/2008 8:16:18 PM
Reply:

Similar Artilces:

Outlook and searching on the Public Folders
Hello, I would like to ask the function of Advanced Search on the Public Folders. When I find any items in the Public Folders search results are only from the folder where I have started to find. But there are no search results from its subfolders - even if there are some items which correspond with the selected search criteria. Could you anyone advise me - is there any possibility to search current folder and also its subfolders? Thank you. Pavel S. Search only works on one public folder, not across multiple Public folders. (It also only works on one message store at a time, but will sea...

Help with search macro not finding
MS Excel 2007 on XP Pro I cannot get this code to work but cannot see anything wrong with it can anyone assist? It runs but ends up on cell B6 presumably not having found the searched for text. Any suggestions will be appreciated. Sub BACK() ' ' BACK Macro ' ' Sheets("School1").Select Range("B5:QJ5").Select Dim varRange As Range Dim varFound As Variant, varSearch As Variant varSearch = Range("A3") Set varRange = ActiveSheet.Range("B5:QJ5") Set varFound = varRange.Find(varSearch, lookat:=xlWhole) If Not varFound I...

How to add patterns to excel tia sal2
Greets All I would like to make/add a pattern for a background cell in excel how can I make one and add it to the options in excel to choose from? Tia Excel has a limited color model and you can't add/subtract from what's offered; Try using the Insert menu > Picture command > from file to import a jpeg or gif image, then size it to the cell. HTH "temp@temp.com" wrote: > Greets All > > I would like to make/add a pattern for a background cell in excel how can I > make one and add it to the options in excel to choose from? > > Tia > ...

How to use of brackets [] and () in search
Searching in the inbox for texts like [work] or (plant) will make outlook search for work and plant. Without the brackets [] or (). How can you search with brackets ? (Outlook 2007 12.0 SP2) You can't do that with Instant Search since the index is word based and not character based. You'll have to use Advanced Find instead. For details see; http://www.msoutlook.info/question/389 -- Robert Sparnaaij [MVP-Outlook] Coauthor, Configuring Microsoft Outlook 2003 http://www.howto-outlook.com/ Outlook FAQ, HowTo, Downloads, Add-Ins and more http://www.msoutlook.info/ ...

search will not locate file
I have a folder full of excel files. I am trying to locate portions of the file by the search function. I place the Folder name in the Locate in box and type in a portion of the containing text. My problem is that it does not seem to go through the search function and immediately comes up with this response: "Search is complete. There are no results to display." However, I can manually open the file and folders within, but is time consuming to find the location of the file that I need. Can someone tell me what I need to do to fix this folder so that it will search by containing tex...

Autocomplete email domain from previous record
Hi All, I have a form with an email address field in which the domain is often the same for each new record. For example, test@domain.com... the "@domain.com" portion is often the same. What I would like to do is somehow have some code that autocompletes just the domain portion from the previous record when the user types the "@" symbol. So, they can then type in "test2" and as soon as they hit the @ symbol it would complete the field with the domain portion from the previous record. Does anyone have a genius way to do this? :-) Thank you for your help!...

searching for mail address in outlook 2007
Hi, When I use instant search in outlook 2007 and search for part of an email address i don't find any mail, although I know for sure they exist in the mailbox. E.g. if I want to find all mail from people working at company X and I know they all have e-mail addresses ...@company_x.com. If I then search for company_x I get zero hits. Why? If I know one of their co-workers is named John Doe and I search for John Doe I find all mails from him. Jonas ...

I can't edit existing records, only add new
I created a form with the intent of being an 'add new contact' form. Well, I want to reuse the form as an edit/update contact form also. The problem is, I can't view any existing records. I have allow edits, deletions, additions enabled. Record locks = no locks. Record selector and navigation buttons = yes. What am i missing? Data Entry property Regards Jeff Boyce Microsoft Office/Access MVP "MeSteve" <MeSteve@discussions.microsoft.com> wrote in message news:11129652-032B-4966-80CC-7AAD8E18162C@microsoft.com... >I created a form with the intent of ...

MultiCurrency Question
Hi: I am in a situation where a canadian based company whose Products and Product List is in Canadian Dollars wants to create a Quote in US dollars. It seems that we cannot add the products with Canadian Dollars Currency in a USD Price List when we are using a Percent method of pricing. If that is the case, how can it be handled? I cannot create 2 sets of the same Product because the exchange rate is not fixed. Typically, CRM should be calculating the currency in USD using the exchange rate that's provided, but CRM simply is refusing to allow me to add a product with a different c...

Re-assign the records from one user to another
Hello, I know that it should be option to re-assign the records on the 'User Definition' screen (under Actions), but when I go into this screen with the SysAdmin role, I don't have this option. Any ideas? Regards, Inna. ...

Search results
Is there a way to have the search results box diplay a darker border or shade the box? ...

Can't add a standard inventory item
Hello, When we select "new" and "standard" we get "runtime error 94 invalid use of null". However we can add other types of items...matrix. We found an article on the knowledgebase and tried those suggestions (they were for Quicksell 2000) but it didn't work. We re-applied the most recent service pack and ran the maintainence and check on the database. Nothing is helping. Anyone seen this before or have any suggestiosn for me. Thanks. Pat Pat@arms wrote: > Hello, > When we select "new" and "standard" we get "runtime error ...

Calculating a value for a new record
I have a simple database which records a list of different reports my team produces. Each report has a unique hopefully sequential number "Report No" which is different to the "Record ID" (Autonumber). I have a form which I am using to add new reports to the underlying table. As I add a new report record I want to look up the highest existing Report No value in the table and auto populate the New Report Form Report No Control with that number +1. i.e. if the last report added was numbered 255 the next one shoiuld be 256. I have tried using Max ([Current ...

Workflow Call Assembly to add Date or Time
I cannot get a very simple workflow to work. All I want it to do is update a datetime field to add one month! Workflow monitor says it completed, but it has never worked... Has anyone ever been able to make this work? On 26 Mar, 18:54, "DubSport" <jamie.carmich...@cmgl.ca> wrote: > I cannot get a very simple workflow to work. All I want it to do is > update a datetime field to add one month! Workflow monitor says it > completed, but it has never worked... Has anyone ever been able to > make this work? Show your workflow rule OK here it is. its a manual rule, s...

How to open a blank form for adding a new record
Hello, I have a form for adding/viewing employee records. I can open the form, view records and using the navigation button, add a new record just fine. However, I'd like to open the form using a command button so that all fields are blank and a new record can immediately been added without having to use the navigation button. Can anyone help me figure out how to do this? TIA, Rich On Tue, 13 Nov 2007 13:20:02 -0800, rich wrote: > Hello, > > I have a form for adding/viewing employee records. I can open the form, > view records and using the navigation button, add a...

Insert row, table to table
Hi, As I cannot use INSERT INTO with values specified to WHERE. I have created an interim temporary table where the record contains two text strings.Now I want to be able to take those text strings and append to the correct table where the record is set by an ID number. First_Name = Fred Last_Name = Jones Its a new contact so it is too be added to a customer with an ID = 1375 Now I can't use the following sql statement strsql = "INSERT INTO [tblCUSTCONTACTS] ( [FIRST_NAME], [LAST_NAME] ) " _ & " VALUES (""" & strFirst & "&...

How do I add LEFT to this array?
=SUMPRODUCT(--(ISERROR(SEARCH("Account Request",'Incident Data'!$E$2:$E$5000))=FALSE),--(MONTH('Incident Data'!$G$2:$G$5000)=MONTH(C$8))) I want to modify it to restrict where it looks for "Account Request". I figure add LEFT('Incident Data'!$E$2:$E$5000,15) however that returns an error. I also tried putting LEFT after SEARCH. I am still very new to SUMPRoduct and I had help coming up with the formula above; which works perfectly now. I am also reading through: http://www.xldynamic.com/source/xld.SUMPRODUCT.html I don't want to break my ...

Update Several Records with separate table data
I have two tables. One has all fields complete. The other has some complete records, but most only have one field completed. I want to input the complete records to the incomplete table, merging any records that would be duplicated. I forgot to mention this in my first post. Why are you duplicating data in your database? The goal of a well designed database is to only store the same piece of information once. Dale -- Email address is not valid. Please reply to newsgroup only. "Dale Fye" wrote: > In Access (am unsure whether A2007 supports this), you can do this as a ...

Create a duplicate record
I use a form to input the data into the table. From time to time, I need to create a duplicate record from previous records or just completed new record. I saw the previous articles here but most of them to copy the last record to a new record. However, I need to highlight the desired record and copy to a new record. Any guidance and example are appreciated. Thanks, Scott Here's one that lets you duplicate the record in a form, as well as any related records in the subform: http://allenbrowne.com/ser-57.html Just ignore the part about the subform, and use the rest. -- ...

Query Does Not Count "2" Records
Good Evening Everyone... I have a bit of a logical puzzle that I need to solve. I have various queries which have various criteria for them. One query involves retirees or spouses being under the age of 65 and the other involves retirees or spouses being over the age of 65. Both the retiree and spouse are listen in the same records and on one line of the master table and queries. The situation that I am running into is that I can have a retiree who is over 65 and a spouse under 65 and the record comes up twice in the two different queries b/c it meets the criteria, which is perfect. What...

Add new account
I have added a new bank account, I selected New and when I click the 'Investment' row, there is a drop menu with very old accounts which dont use anymore. Where can these accounts be viewed, and deleted as necessary? Thank you ...

Automatically add 1 until a number is found
Hiya, I am developing a spreadsheet which basically grosses-up a paymen which i have to make to an employee. What I'd like to do is take a cell and add 1 automatically until th number equals a specific equation. Is this feasible? Thanks Jame -- Message posted from http://www.ExcelForum.com Hi Have a look at Tools / Goal Seek. It might be what you are looking for. -- Andy. "jkyndt >" <<jkyndt.1a3rda@excelforum-nospam.com> wrote in message news:jkyndt.1a3rda@excelforum-nospam.com... > Hiya, > > I am developing a spreadsheet which basically grosses-u...

add from option to new mrssages
I want to add the option of choosing which account I am sending my mail from as I manage multiple accounts. "Sue" <Sue@discussions.microsoft.com> wrote in message news:701DABFB-2B8B-46CB-B21A-98F646FFA74A@microsoft.com... > I want to add the option of choosing which account I am sending my mail > from > as I manage multiple accounts. Click the Account button in the New Message window.... ...

How do I add a disclaimer to my outgoing emails
I want to add a disclaimer to the bottom of all my outgoing emails, after my signature. How do i do that please? > I want to add a disclaimer to the bottom of all my outgoing emails, > after my signature. How do i do that please? I hate to submit the obvious but: Add it to your signature, at the bottom of it? -- f.h. Microsoft Outlook MVP See http://www.slipstick.com/addins/sig.asp for tools. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com Outlook Tips by email: mailto:dailytips-sub...

Can't add file to VC7 project
I'm sure this is a very idiotic question, but I can't seem to add file.h and file.cpp to my project.. -Both files are in the same folder with the other project files. -I did a File/add existing item/ then picked the files from the dialog. -Clicked open. But then Solution Explorer did not show the files??? (It does work OK on another computer with same software.) Why might this be??? Thanks, E-Jo Hello Everett, > I'm sure this is a very idiotic question, but I can't seem to > add file.h and file.cpp to my project.. > -Both files are in the same folder with the ...