Make combobox blank in macro

  • Thread starter Thread starter Song
  • Start date Start date
S

Song

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.
 
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
 
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
 
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.






- Show quoted text -
Click to expand...

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.
 
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
 
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.








- Show quoted text -
Click to expand...

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?
 
open the form in design view
Tools
Macro
Convert

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