Excel Userform with data validation from Access DB

[padadof2]

I am trying to make a userform that we can use to import information that is in a worksheet named "Index" as well as other information to into our access Database. When we get a new project, we then take the owners name and information, the Contractors name and information and put into our database. We don't have any data validation, so there are multiple entries for the same owner, so I would like to have a dropdown list in the userform from the DB, so I can see if I need to add a new owner or if that owner already exists. I'm not sure if its even possible. right now I have a worksheet in my network with the owners we already have, but its not dynamic when I add another owner. Thanks in Advance

 

[Micron]

A bit confusing as you've posted this in Excel but it seems you're asking about an Access form. Userform is specific to Excel; in Access it is just "form" so that's part of the confusion for me. Nor do I see where the sheet comes into this if you are entering data into Access directly. I'm going to assume your user is entering data into Access, not Excel.

First off, your table needs to have a unique index on Owner field so that you cannot add the same value more than once. If it is possible that there could be 2 values the same (after all, there could be 2 owners named John Smith?) then you need another way to create a unique index. A common one is to create a unique composite index. Regardless, it is the index that will prevent duplicate entries so that if your combo does not show John Smith in the list, it is because it is not there, which will automatically answer the question.

I have a worksheet in my network with the owners we already have, but its not dynamic when I add another owner.

On the other hand, if you're adding owners to a sheet then forget what I wrote because I'm just confused. I will caution you about one thing though. After reading hundreds of posts from Excel users who create databases I can say that most, if not all of them, get the table design wrong. They design tables as they would spreadsheets because they do not know about relational database design. You would be wise to apply db normalization concepts if it is not too late.

 

[padadof2]

I'm sorry for the confusion. Let me try to explain the work flow I am thinking. I am a hack, at best and might be out of my league, but here I go. We use excel spreadsheets to set up our bid estimates. This workbook has a worksheet named "Index" that has some information we need if the estimate turns into a project. When one of our bid estimates turns into a project, we input the project into our DB. We have an existing DB with four tables in them. One is "owner", one is "prime" one is "engineer" and the last one is "generalinfo". "generalinfo" is the table that we enter our projects into. That table has our owners, primes, and engineers in it, as well as the information for that specific project. If the owner, engineer or prime is new, we need to add them to their tables first. My goal is to be able to query the database to see if the owner/prime/engineer is existing, or if we have to create a new record in the owner/prime/engineer table. My first thought was to use data validation and a dropdown in the userform, so we won't get multiple entries for the same owner/prime/engineer. I hope this clears up what I'm trying to accomplish. Not sure if I should be looking at this workflow on access side or excel side. Thanks again for any help.

 

[RoryA]

Userform is specific to Excel;

Actually, all Office apps can use userforms (including Access).

 

[Micron]

If you meant Access can use an Excel userform in Access then that's news to me. Have any links that explain that? A quick look and I found nothing but would sure like to read up on that.

If you're calling an Access form a userform, I'll say that in any Access forum I've belonged to, you will get corrected sooner or later. Access forms are "forms" they are not "userforms". Userforms are quite limited in events, methods and properties compared to Access forms.

 

[RoryA]

No, I'm saying that Access has userforms just like Word, Excel, Outlook and PowerPoint do. They are not just an Excel feature.

They are not often used in Access given that it has its own forms which are much better at working with data, but they are still there.

 

[Micron]

@padadof2
If it were me, I'd create the database with properly normalized tables and link to the worksheet as a table, assuming it doesn't ever change it's name. If it does periodically, then I'd probably import from Excel when necessary. The end goal being to normalize the tables and distribute the sheet data amongst them, thinking that seldom does a sheet make for a good table. I think normalized tables is where you're about to falter. If prime is a person, then for those people you should probably have one table, not 3. If you repeat field data (or could possibly need to do so in the future) because you can have 2 or more of those people identified (as what I don't know) then you definitely have a problem there. Example

WHO	WHAT	START	END	OTHER STUFF
owner1	A project	1/25/22	5/30/22	AS;KDFJASLKFJ
engineer	A project	1/25/22	5/30/22	AS;KDFJASLKFJ

My goal is to be able to query the database to see if the owner/prime/engineer is existing,

Properly normalized tables would take care of that, especially if the data you're looking for is in the same table. If it's not and should not be, then you examine a query result that brings those table fields together. Consider doing some research (might as well give you all the important novice stuff):
Normalization Parts I, II, III, IV, and V

What Is Normalization, Part I

Thoughts, opinions, samples, tips, and tricks about Microsoft Access

rogersaccessblog.blogspot.com

and/or

Database Normalization – Holowczak.com Tutorials

holowczak.com

Entity-Relationship Diagramming: Part I, II, III and IV

Entity-Relationship Diagramming: Part I

Thoughts, opinions, samples, tips, and tricks about Microsoft Access

rogersaccessblog.blogspot.com

How do I Create an Application in Microsoft Access?

How do I Create an Application in Microsoft Access?

Thoughts, opinions, samples, tips, and tricks about Microsoft Access

rogersaccessblog.blogspot.com

Important for success:
Naming conventions - General: Commonly used naming conventions

MS Access Naming Conventions

_________________________________________________________ Naming Conventions. This document is aimed at the user who is unfamiliar with any naming convention. It is based upon personal experience and the interaction I have had with others. It is not a hard and fast rule. Why use a...

www.access-programmers.co.uk

What not to use in names
- Microsoft Access tips: Problem names and reserved words in Access

About Auto Numbers
- General: Use Autonumbers properly

The evils of lookup fields - The Access Web - The Evils of Lookup Fields in Tables
Table and PK design tips - Microsoft Access Tables, Primary Key Tips and Techniques
About calculated table fields - Microsoft Access tips: Calculated Fields
About Multi Value Fields - Multivalued Fields

 

[Micron]

Technically you are in the VBE there, not Access but I suppose that's a minor distinction. Userforms are actually part of the VB language reference (VBIDE), which means they're exposed to other Office apps that use vba. I'll admit I learned something, so thank you. Maybe I'll play with that later out of curiosity. Have you managed to code anything with respect to doing that in an Access code project?

 

[RoryA]

Technically you are in the VBE there, not Access

Well yes, but that is true of any application that uses userforms. You have to build them in the VBE.

Userforms are part of the MSForms library rather than the VBIDE one. I don't really use Access other than as a data store but I have used them occasionally in the past for simple informational forms that aren't based on any data in the database, as I can usually import something similar from an Excel app and tweak it as needed. I'd never use them for actual data work though - I have often lamented the inability to use Access forms in Excel!

 

[padadof2]

@Micron forgive my ignorance with access, I use excel way more. Here is the generalinfo table set up in our database.

when you get to the prime/owner/eng arch columns within access, there is a dropdown that pulls from the other tables, EngineerArchitect, Owner, and PrimeContractors. Right now, I have the userform in excel set up to open the database, and add a new record to the generalinfo table. Where I'm running into trouble, is if there is a new record that isn't already in our owner/engineer/prime tables. I hope this helps. I do appreciate the help!