By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,248 Members | 1,961 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,248 IT Pros & Developers. It's quick & easy.

Get column source through VBA

P: 64
Hi There

In my table I have a column for which I have specified Display Control as "Combo Box" and have put a query (from master list) as Row Source

Is it possible to get the Row Source query text (of the column within the table) through VBA

something like
qry = CurrentDb.OpenRecordset("Trade Details").Fields("C/P Legal Entity").rowsource

output will be "select * from MasterList"

I am trying to import an excel spreadsheet into the table through VBA and before importing I would like to check if the values for the column (in excel) are within the master list

Since I have many fields which have this kind of combobox, I would like to automate it instead of writing a separate code for each field

Thanks a ton
Feb 4 '09 #1
Share this Question
Share on Google+
2 Replies

Expert 100+
P: 1,287
You could use a function to determine whether a value is in the "MasterList" like:

Expand|Select|Wrap|Line Numbers
  1. public function isInList (value as String) as Boolean
  2.     isInList = false
  3.     if not isnull(DLookUp ("FieldName", "MasterList", "FieldName = """ & value & """") then
  4.         isInList = True
  5.     end if
  6. end function
You could make the table or fieldname an input parameter also, if it mattered.
Code is off the top of my head, not tested.
Feb 4 '09 #2

Expert 5K+
P: 8,624
To retrieve the Row Source of a Column in a Table:
Expand|Select|Wrap|Line Numbers
  1. Dim strSQLRowSource As String
  2. '
  3. strSQLRowSource = CurrentDB.TableDefs("<Table Name>").Fields("<Field Name>").Properties("RowSource")
Feb 4 '09 #3

Post your reply

Sign in to post your reply or Sign up for a free account.