[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Form with complex sourc

Posted on 2012-08-19
8
Medium Priority
?
641 Views
Last Modified: 2012-08-19
I've got a form "frm_ControlContact" that has as source "tbl_ControlContact", which has fields "RoleID" and "ContactID".

I need to add/update/delete the table through the form.   I do combobox lookups on the form to find RoleID and ContactID in tbl_Role and tbl_Contact.   I then need to display the RoleAbbrev associated with RoleID and the ContactName associated with ContactID.

If I do multiple joins in the source query I run into Dynaset Inconsistent issues which I would like to avoid.

I can't figure out how to use DLookup to get the result I want.   RoleAbbrev and ContactName are unbound.  The easy way would be to make them bound fields in tbl_ControlContact but that seems inelegant.

Any suggestions on this would be appreciated.

Thanks!
0
Comment
Question by:codequest
  • 4
  • 4
8 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 38310106
Take a look at this sample and see if it is what you're looking for.  The combo saves the ID in the underlying table, but displays the name.

The relevant properties are

Column count: 2
Column Widths: 0; 1.8
Rowsource: SELECT ID, Full_Name FROM Employees

The ID column with this setup is hidden; the text displays, and the numeric ID is stored.
test.accdb
0
 
LVL 2

Author Comment

by:codequest
ID: 38310335
Thanks for the suggestion.  What I'm trying to do is indicated in the attached.  I realized that I'm making it much more complicated because I want to use a separate unbound field to show the result of the combobox lookup.  But there's got to be a way...

I'll look at the conventional approach you described and see if I can get that to do what I want. However, I would really like to get my approach to work.   So any thoughts would be appreciated.
test.accdb
0
 
LVL 61

Accepted Solution

by:
mbizup earned 2000 total points
ID: 38310362
This is another method that will work (similar to what you are describing):

- Set the rowsource of your Role combo to include the Role ID and Role Abbreviation (it might already be set up like this)
   SELECT RoleID, RoleAbbr FROM tblRoles
- Set the control source of your unbound textbox to (include the = sign):
    = YourComboName.Column(1)

- Take a similar approach with your other combo.
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 2

Author Comment

by:codequest
ID: 38310419
Thanks, that looks promising.  I'll work on it tomorrow.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38310425
Just a note about the column index ... I'm using 1 to refer to the second column.

Column count is zero based so the first column is column zero, then 1,2, etc.
0
 
LVL 2

Author Comment

by:codequest
ID: 38310493
Couldn't wait.  Tried it.  Works slick. Lifesaver!  Grazie!
0
 
LVL 2

Author Closing Comment

by:codequest
ID: 38310494
Very educational too, about that particular point of what the combo box returns.  It's dynamic (i.e. will update properly on recordsource requery.)  Just what was needed.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38310547
Glad that worked out :-)
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question