A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Make combobox blank in macro



 
 
Thread Tools Display Modes
  #1  
Old April 21st, 2010, 03:30 PM posted to microsoft.public.access
Song[_4_]
external usenet poster
 
Posts: 43
Default Make combobox blank in macro

I use a wizard to create a combo box to find the record in my form. It
creates macro automatcially. Works fine. After finding the record, I
want to set the combo box to null or "" but need help add that line in
macro.

Thanks.
  #2  
Old April 21st, 2010, 08:38 PM posted to microsoft.public.access
Wayne-I-M
external usenet poster
 
Posts: 3,674
Default Make combobox blank in macro


Sorry but not many people use macros (me too) so can’t really be help there.
But try this.

I assume your macro is search on a name ?
You have the ID field in the combo and the name and the ID field is the
bound column of the combo ???

If so.

1st MAKE A BACKUP OF YOUR DATABASE

Then – open you form in design view
Right click the combo and open the Properties box
In the Event column go to the After update row
Right click a use the build option (…)
Select Code
Add this to the code


Private Sub NameOfComboHere _AfterUpdate()
Dim rs As Object
Set rs = Me.recordset.Clone
rs.FindFirst "[ NameOfIDfieldHere] = " & Str(Me![ NameOfComboHere])
Me.Bookmark = rs.Bookmark
Me. SomeOtherControlOnFormHere.SetFocus
Me. NameOfComboHere = ""
End Sub

You need to change these bit of the code to what the name really are on your
form
NameOfComboHere NameOfIDfieldHere SomeOtherControlOnFormHere

You need to set the focus to SomeOtherControlOnFormHere before you can set
the vaule of your combo to “”

You can slightly improe on the above by add something like this to your combo
properties (the Not In List row)
This will add a new record if the person you are searching for not in your
database
(but it will ask you 1st)

Change NameOfComboHere SurnameHere to what they really are in your database

Private Sub NameOfComboHere _NotInList(NewData As String, Response As Integer)
Dim Db As DAO.Database
Dim rs As DAO.recordset
Dim Msg As String
Msg = "'" & NewData & "' is not on file." & vbCr & vbCr
Msg = Msg & "Do you want to add New Record?"
If MsgBox(Msg, vbQuestion + vbYesNo) = vbNo Then
Response = acDataErrContinue
MsgBox "Try again."
Else
Set Db = CurrentDb
Set rs = Db.OpenRecordset("TableNmeHere", dbOpenDynaset)
rs.AddNew
rs![SurnameHere] = NewData
rs.Update
Response = acDataErrAdded
End If
End Sub


Hope this helps


--
Wayne
Manchester, England.



"Song" wrote:

I use a wizard to create a combo box to find the record in my form. It
creates macro automatcially. Works fine. After finding the record, I
want to set the combo box to null or "" but need help add that line in
macro.

Thanks.
.

  #3  
Old April 21st, 2010, 08:45 PM posted to microsoft.public.access
Wayne-I-M
external usenet poster
 
Posts: 3,674
Default Make combobox blank in macro

Hi again

I have just checked on how to use macros - sorry don't use them very often

If you really wan tto use the macro
open the macro in desgin view
add a new line (row) "under" the ones you already have
Select - Go To Control - add the name of another control on your form "not"
the
combo
Note you have to do this
Add another line and select Set Value - add you combo to the Item row
In the Expression row add "" -
thats 2 x " by the way

Hope this helps


--
Wayne
Manchester, England.



"Song" wrote:

I use a wizard to create a combo box to find the record in my form. It
creates macro automatcially. Works fine. After finding the record, I
want to set the combo box to null or "" but need help add that line in
macro.

Thanks.
.

  #4  
Old April 22nd, 2010, 04:01 AM posted to microsoft.public.access
Song[_4_]
external usenet poster
 
Posts: 43
Default Make combobox blank in macro

On Apr 21, 12:45*pm, Wayne-I-M
wrote:
Hi again

I have just checked on how to use macros - sorry don't use them very often

If you really wan tto use the macro
open the macro in desgin view
add a new line (row) "under" the ones you already have
Select - Go To Control *- add the name of another control on your form "not"
the
combo
Note you have to do this
Add another line and select Set Value - add you combo to the Item row
In the Expression row add *"" *-
thats 2 x " by the way

Hope this helps

--
Wayne
Manchester, England.



"Song" wrote:
I use a wizard to create a combo box to find the record in my form. It
creates macro automatcially. Works fine. After finding the record, I
want to set the combo box to null or "" but need help add that line in
macro.


Thanks.
.- Hide quoted text -


- Show quoted text -


Hi, Wayne:

It works! I'm using Access 2007 so wizard creates macros instead of
sub/end automatically. So I just try to learn macro as it does not
create module.

Now what's the macro for 'dropdown' at 'gotfocus' event? Thanks.
  #5  
Old April 22nd, 2010, 07:17 AM posted to microsoft.public.access
Wayne-I-M
external usenet poster
 
Posts: 3,674
Default Make combobox blank in macro

Hi

You really need to try at least to not use macros. There is only so much
you can do with them.

Have a go and see what happens like this.
Open you form in design view.
Right click your combo
Open the properties box
In the event coloum select the Got Focus row
Right click and select build (...)
Add this in between the 2 lines on code that are already there

Me.NameOfCombo.DropDown

(Change the NameOfCombo to what it really is)
Save

Now when the combo have the focus it will "drop down"

Hope this helps

--
Wayne
Manchester, England.



"Song" wrote:

On Apr 21, 12:45 pm, Wayne-I-M
wrote:
Hi again

I have just checked on how to use macros - sorry don't use them very often

If you really wan tto use the macro
open the macro in desgin view
add a new line (row) "under" the ones you already have
Select - Go To Control - add the name of another control on your form "not"
the
combo
Note you have to do this
Add another line and select Set Value - add you combo to the Item row
In the Expression row add "" -
thats 2 x " by the way

Hope this helps

--
Wayne
Manchester, England.



"Song" wrote:
I use a wizard to create a combo box to find the record in my form. It
creates macro automatcially. Works fine. After finding the record, I
want to set the combo box to null or "" but need help add that line in
macro.


Thanks.
.- Hide quoted text -


- Show quoted text -


Hi, Wayne:

It works! I'm using Access 2007 so wizard creates macros instead of
sub/end automatically. So I just try to learn macro as it does not
create module.

Now what's the macro for 'dropdown' at 'gotfocus' event? Thanks.
.

  #6  
Old April 22nd, 2010, 04:43 PM posted to microsoft.public.access
Song[_4_]
external usenet poster
 
Posts: 43
Default Make combobox blank in macro

On Apr 21, 11:17*pm, Wayne-I-M
wrote:
Hi

You really need to try at least to not use macros. *There is only so much
you can do with them.

Have a go and see what happens like this.
Open you form in design view.
Right click your combo
Open the properties box
In the event coloum select the Got Focus row
Right click and select build (...)
Add this in between the 2 lines on code that are already there

Me.NameOfCombo.DropDown

(Change the NameOfCombo to what it really is)
Save

Now when the combo have the focus it will "drop down"

Hope this helps

--
Wayne
Manchester, England.



"Song" wrote:
On Apr 21, 12:45 pm, Wayne-I-M
wrote:
Hi again


I have just checked on how to use macros - sorry don't use them very often


If you really wan tto use the macro
open the macro in desgin view
add a new line (row) "under" the ones you already have
Select - Go To Control *- add the name of another control on your form "not"
the
combo
Note you have to do this
Add another line and select Set Value - add you combo to the Item row
In the Expression row add *"" *-
thats 2 x " by the way


Hope this helps


--
Wayne
Manchester, England.


"Song" wrote:
I use a wizard to create a combo box to find the record in my form. It
creates macro automatcially. Works fine. After finding the record, I
want to set the combo box to null or "" but need help add that line in
macro.


Thanks.
.- Hide quoted text -


- Show quoted text -


Hi, Wayne:


It works! I'm using Access 2007 so wizard creates macros instead of
sub/end automatically. So I just try to learn macro as it does not
create module.


Now what's the macro for 'dropdown' at 'gotfocus' event? Thanks.
.- Hide quoted text -


- Show quoted text -


Hi, Wayne:

Yes. I'm learning VBA and it's much more powerful than macro. However,
Whenever I run wizard, it generates macro instead of vba (I'm using
Access 2007). Combo box wizard generates one line of macro to find a
record. Command button wizard generates 6 lines of macro to delete a
record. Is there a way to set wizard to generate VBA instead of macro?
If not, is there a way to convert wizard generated macro to vba?
  #7  
Old April 22nd, 2010, 07:51 PM posted to microsoft.public.access
Wayne-I-M
external usenet poster
 
Posts: 3,674
Default Make combobox blank in macro

open the form in design view
Tools
Macro
Convert

Then view the code to see what the macro would look like in vba

--
Wayne
Manchester, England.



"Song" wrote:

On Apr 21, 11:17 pm, Wayne-I-M
wrote:
Hi

You really need to try at least to not use macros. There is only so much
you can do with them.

Have a go and see what happens like this.
Open you form in design view.
Right click your combo
Open the properties box
In the event coloum select the Got Focus row
Right click and select build (...)
Add this in between the 2 lines on code that are already there

Me.NameOfCombo.DropDown

(Change the NameOfCombo to what it really is)
Save

Now when the combo have the focus it will "drop down"

Hope this helps

--
Wayne
Manchester, England.



"Song" wrote:
On Apr 21, 12:45 pm, Wayne-I-M
wrote:
Hi again


I have just checked on how to use macros - sorry don't use them very often


If you really wan tto use the macro
open the macro in desgin view
add a new line (row) "under" the ones you already have
Select - Go To Control - add the name of another control on your form "not"
the
combo
Note you have to do this
Add another line and select Set Value - add you combo to the Item row
In the Expression row add "" -
thats 2 x " by the way


Hope this helps


--
Wayne
Manchester, England.


"Song" wrote:
I use a wizard to create a combo box to find the record in my form. It
creates macro automatcially. Works fine. After finding the record, I
want to set the combo box to null or "" but need help add that line in
macro.


Thanks.
.- Hide quoted text -


- Show quoted text -


Hi, Wayne:


It works! I'm using Access 2007 so wizard creates macros instead of
sub/end automatically. So I just try to learn macro as it does not
create module.


Now what's the macro for 'dropdown' at 'gotfocus' event? Thanks.
.- Hide quoted text -


- Show quoted text -


Hi, Wayne:

Yes. I'm learning VBA and it's much more powerful than macro. However,
Whenever I run wizard, it generates macro instead of vba (I'm using
Access 2007). Combo box wizard generates one line of macro to find a
record. Command button wizard generates 6 lines of macro to delete a
record. Is there a way to set wizard to generate VBA instead of macro?
If not, is there a way to convert wizard generated macro to vba?
.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 09:26 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright 2004-2024 OfficeFrustration.
The comments are property of their posters.