converting a field/column from text to combo box (1 Viewer)

fst · Apr 9, 2018

hi everyone. hopefully someone can help me on this particular issue. right now I have a make table query and one of the fields I created is just blanks. what I would like to do is after the make table, to have in vba coding to turn the one field, lets call it Plan/No Plan, convert it to a combox box so that other users that use my database can go into that table and select from a drop down selection to choose "Plan" or "No Plan" for the particular line/row.

thanks in advance for your help!

Frothingslosh · Apr 9, 2018

You should never, ever, under any circumstances have your users modifying tables directly.

Forms exist to provide the user a legible interface while at the same time allowing developers to restrict and/or validate code and do all SORT of other handy things like print reports, calculate totals, automate any number of mass or single updates, etc.

Furthermore, you cannot attach VBA to a table. They have no events to trigger - the events are accessed from forms and reports.

So in a nutshell: Nope, not gonna happen.

fst · Apr 9, 2018

hmm thanks for the heads up Frothingslosh. my original idea was to post the table out to SharePoint and have the table available for the field/users to update the row for the widget whether it has a Plan or No Plan yet. once the widget gets updated then the users like myself will know that the status for the widgets in the table. does that make sense?

bastanu · Apr 9, 2018

Hi FST,

While agreeing with Frothingslosh that you should try to limit the user's interaction with the tables themselves, you can achieve your desired end result by replacing the make-table query with a pair of delete/append queries. Those will not alter your table structure where you could go in and manually set the field to be a lookup with the values Plan/No Plan.

Cheers,
Vlad

fst · Apr 9, 2018

bastanu said:
Hi FST,

While agreeing with Frothingslosh that you should try to limit the user's interaction with the tables themselves, you can achieve your desired end result by replacing the make-table query with a pair of delete/append queries. Those will not alter your table structure where you could go in and manually set the field to be a lookup with the values Plan/No Plan.

Cheers,
Vlad

Vlad, hi there, could you elaborate on what you mean by using the delete/append query in lieu of the make-table please?

bastanu · Apr 9, 2018

Run your make table query to create the table, then modify it to suit your needs (open it in design view and change the field to a lookup field). To populate it for subsequent times run first a delete query (DELETE * FROM YOUR_TABLE

to remove all records then run an append query to add the latest records (hint: if you follow these steps you can very easily change the make-table into an append query, just change the type of query and Access will preserve your field mappings).

Cheers,
Vlad

fst · Apr 9, 2018

Vlad,
thanks for elaborating. my question is rather than manually open it in design view everyday, if it'd be possible to have it coded in vba so I can run it via macro via task scheduler to automatically run it?
after figuring out how to change the column/field fo the Plan/No Plan, you're suggesting doing the following next? (paraphrasing):
create a make table so all of the field names is set up
next step is a delete query to remove all records
run an append query (from what data if it's been deleted unless you mean the source data (which is a SQL view?)

sorry for the noob questions, I honestly rarely use the append query in a majority of my databases fwiw, just make table queries lol.

bastanu · Apr 9, 2018

That's my point, you don't do it everyday, it is a one-time deal (go to tables, right click the newly created table by your make-table, click on the field you want to edit, edit its lookup properties and save the table). From now on by using delete/append you are not changing the structure of the table, the lookup plan/no plan will be there after every update. There is also a another advantage of using a delete/append, you can setup your table properly (adding primary keys, indexes, etc.) which a make-table doesn't allow.

Cheers,
Vlad

fst · Apr 9, 2018

I tried the delete * from my_table and it removed the field that I changed to a combo box?

bastanu · Apr 9, 2018

impossible, a DELETE * query only removes the data from a table, it does not ALTER the table. Is it possible to create a new Access file and put the empty table and the make-table query in it so we could have a look? You will need to include the empty tables the make-table is based on (preferably).

Cheers,
Vlad

fst · Apr 11, 2018

Vlad, you're correct, sorry about that. I re-ran and the table still has the list box. I'm still wrapping my head around on how to design this. basically the premise is have a table that's uploaded to sharepoint for users to edit the "Plan/No Plan" field on various widgets each day. the table will also refresh everyday as one widget will drop off while another new widget will be added to the overall table.

I'm thinking this will need a way of creating a temp table of the existing/prior table and then comparing it today's data and see if there's any that dropped off and remove them then do another comparison of the today's table on new ones? so its a 2 phase query or vba coding?

bastanu · Apr 11, 2018

Do you create the table first as local Access table and then upload it to Sharepoint (overwriting the old one)? If yes maybe you can link the Sharepoint table and use it in your append query (with a left outer join) to preserve the values already there. Otherwise use a make-table to store the primary key (widget) and Plan/No Plan in a temporary table then use that in an update query after you run the append.

Cheers,
Vlad

converting a field/column from text to combo box (1 Viewer)

fst

Registered User.

Frothingslosh

Premier Pale Stale Ale

fst

Registered User.

bastanu

AWF VIP

fst

Registered User.

bastanu

AWF VIP

fst

Registered User.

bastanu

AWF VIP

fst

Registered User.

bastanu

AWF VIP

fst

Registered User.

bastanu

AWF VIP

Similar threads

Users who are viewing this thread