Double click combo box to add data

[sam49]

Hi can someone help me please,
I am using Access 2010 and I have a database of my large book collection, and I have a form that I input all the data into the database.
I have a separate table for the book authors and a separate form to add data the authors table, but what I want to do is when I am adding data to the database and when I get a new author I would like to double click on the combo box on my input form and that then opens the form for adding data to the authors table this letting me select the new author without having to leave the input form.
I know this can be done as I am using a template database from Microsoft and it has this facility for the book topic, and I would like to add this facility to the book author.


I have also seen a contact database form Microsoft and this you are able to double click on a field to add data to a table.
So can anyone tell me how to do this please?

[PrintShopSup]

You can open the Property sheet for the combobox and add code to the OnDoubleClick Event to open your form to add an Author.

[sam49]

Ok thanks but i dont know what code to add

[10 Gauge]

Ok thanks but i dont know what code to add

You could create an OpenForm macro and then have the OnDoubleClick event point to the macro.

[sam49]

Ok I have created a table to store the names of people, who read audio books, then I have created a input form to add new books, on this input form I have created a combo box using the control wizard to select data from the book narrator table and it works just fine.
Now my problem I need to be able to double click on the combo box to add new data to the narrators table.
I also understand I need to go to the properties of the combo box and click on the event tab and then on not in list box.
If I click on the small down pointing arrow I get [Event Procedure] then if I click on the three … dots after the small pointing down arrow I get a dialog box with three choices
1 Macro builder
2 Expression Builder
3 Code Builder

I believe if I am right I need the code builder option

If then click on the space just before the down the small down pointing arrow and get the [Event Procedure] then click on the small just to the right I get Visual Basic box with some code in it.

I am then lost so please what do I do now on top of the visual basic window just below the toolbar I have selected the combo box name and then over to the right I have selected not in list.

How do I save this code that’s if I have to save it?

Then I have done the same for the Double click option

But nothing seems to work I still am unable to double click to add a record

[10 Gauge]

If you want to add data directly to the table, without the use of a form for data entry, the easiest option would be to use the Macro Builder function. When the macro builder opens, set the action to "OpenTable" and then under Arguments you can select your table from the drop down "Table Name" box at the bottom of the screen. Then when you double click on the combobox it will open the table for you.

[sam49]

Ok thanks 10 Gauge i will give that a try

Wow i have tried it and it works brilliant i have been trying to do this for weeks and weeks.

I have linked the macro to open a data input form i just need to work out now how to get it to open the form in the new record position.

Thank you so much

[10 Gauge]

Ok thanks 10 Gauge i will give that a try

Wow i have tried it and it works brilliant i have been trying to do this for weeks and weeks.

I have linked the macro to open a data input form i just need to work out now how to get it to open the form in the new record position.

Thank you so much

Try this for having it open as a new record. Go back to your Macro Builder, I assume you are using the "OpenForm" action. Now at the bottom there should be a place to enter "Where Condition", in that box put in "1=0" (without the " "). Save it and run your macro, let me know if that worked and brought up a blank form for you.

[sam49]

Hi 10 Gauge.

I will get back to you on this surgestion as i have to go away for a couple of days and i will test it out when i get back
Regards

[Mill21]

Hi sorry posted in error

[sam49]

Hi 10 Gauge

Just got back and tried your suggestion and it works brilliantly thank you so much.
Just one more question if i may please

After this macro runs and open the form in the new record position and i have added the new record, how doi get it to refresh the table and make the new entry visible in the main inptu form, as it is now i have to close the input form and restart it to be able to see the new record

Thank you for the help you have given me so far

[10 Gauge]

Not a problem.

What you need to have happen is a requery of the main form after your data has been entered on your entry form. There are a number of ways you can do this.

How do you have your input form set up? Do you have a button for "Save & Next" or "Close" or "Ok"? If you go to the properties for the button, then under the Event tab, you can set an On Click event (so that when the button is pushed, for arguments sake lets say to close the form, it will force a specified event to fire before closing the form).

Another way you could do this would be to set an AfterUpdate event on the last entry box on your input form, so that after you tab away, or lose focus on the last entry box it will fire an event.

When you choose your event, hit the ..., and chose code builder. The code you would use to requery your main form would be Forms!YourForm.Requery (where "YourForm" is the name of your main form, make sure to put brackets around the form name if there are spaces in it.)

Let me know if that works.

[sam49]

Hi
My main input form is set up so when i have entered a new record i just click on the navigation buttons on the bottom of the form for new record and that moves me to a blank form and saves the last data.

I will try out you advice tonight when i get home and post back

I might be wrong but at the moment i am adding data to a book collection i have a main input from to enter the main data, but when i get a new author i now use your macro idea to open a form to add a the new author, the new author form now opens in the new record position allowing me to enter the new data.

So i might try your button idea on the new author form so the new data is then visible in the data input form after the new author form is closed is that the thing to do.

[10 Gauge]

I actually think the AfterUpdate event would work best, but it's totally up to you, both will give the same end result.

[sam49]

Hi 10 Gauge

I have tried your button with a little success on my new Author form i added a button to add new dta and then one the update the form data and then one to close the form, At first it seemed to work ok, i could have my main input table open and then need to add a new author and if the data was not in the list the add new author form opened i could then add the new author then refresh the form data then close the form, then back in the main data input form i could select the new author. but after i while it works about 1 in 10 time.

The add new record button works ok any new data is added to the relivant table but i am not sure if the refresh form data button works every time.

And i am unable to get the afterUpdate to work at all

Regards