UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Populate A Combo Box, Access 2016    
 
   
alorenzini
post May 16 2019, 08:28 AM
Post#1



Posts: 808
Joined: 21-September 07
From: South Dakota


I am not sure if I am working the title correctly. What I need to do is , I have a combo box called cmbUnit which has the following query:

Select unitid, unitcode from tblUnit.

Now I have a unbound textbox called txtTenant, which I need to be populated based on the value select from the cmbUnit. But the gotcha is that that value will need to be looked up in another table. I am trying to work out the code for After_Update event of the cmbUnit control. I am thinking it will be something like:

Select Locncode, Name from BA4602200 where locncode = cmbUnit.olumn(1).Value

Or something like that.

Thank you,

Art Lorenzini
Sioux Falls, SD

--------------------
Thanks

Art Lorenzini
Sioux Falls, SD
Go to the top of the page
 
Doug Steele
post May 16 2019, 08:54 AM
Post#2


UtterAccess VIP
Posts: 22,149
Joined: 8-January 07
From: St. Catharines, ON (Canada)


If you're trying to populate an unbound textbox, simply having the SQL for a query likely won't do you any good.

Try something like:

CODE
Private Sub cmbUnit_AfterUpdate()
  
  If IsNull(Me.cmbUnit) = False Then
    Me.txtTenant = DLookup("Name", "BA4602200", "locncode = " & Me.cmbUnit.Column(1))
  End If
  
End Sub

This assumes that locncode is a numeric field. If it's text, try

CODE
Private Sub cmbUnit_AfterUpdate()
  
   If IsNull(Me.cmbUnit) = False Then
     Me.txtTenant = DLookup("Name", "BA4602200", "locncode = '" & Me.cmbUnit.Column(1) & "'")
   End If
  
End Sub

--------------------
Doug Steele, Microsoft Access MVP (2000-2018)
Personal webpage
Microsoft profile
Co-author: Access Solutions: Tips, Tricks, and Secrets from Microsoft Access MVPs, published by Wiley
Co-author: Effective SQL: 61 Specific Ways to Write Better SQL, published by Addison-Wesley Professional
Technical Editor: Access 2010 Bible, Access 2013 Bible, Access 2016 Bible, all published by Wiley
Technical Editor: SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition, published by Addison-Wesley Professional
Go to the top of the page
 
ADezii
post May 16 2019, 08:57 AM
Post#3



Posts: 2,297
Joined: 4-February 07
From: USA, Florida, Delray Beach


Here is an example as to how this can be done with a single Field Lookup. You should be able to figure the rest out, but if not, we are here for you:
CODE
Private Sub cmbUnit_AfterUpdate()
Dim varLookup As Variant

With Me
  If IsNull(!cmbUnit) Then
    ![txtTenant] = ""
      Exit Sub
  End If
  
  varLookup = DLookup("[Name]", "BA4602200", "[locncode] = '" & ![cmbUnit].Column(1) & "'")
  
  ![txtTenant] = Nz(varLookup, "N/A")
End With
End Sub


P.S. - Bad idea using the Field Name of [Name] in the BA4602200 Table.
@Doug:
Sorry, looks like the old 'Simultaneous Post! laugh.gif
This post has been edited by ADezii: May 16 2019, 09:05 AM
Go to the top of the page
 
alorenzini
post May 16 2019, 09:15 AM
Post#4



Posts: 808
Joined: 21-September 07
From: South Dakota


Its actually a text field.

--------------------
Thanks

Art Lorenzini
Sioux Falls, SD
Go to the top of the page
 
theDBguy
post May 16 2019, 10:07 AM
Post#5


Access Wiki and Forums Moderator
Posts: 75,279
Joined: 19-June 07
From: SunnySandyEggo


Hi Art. It would be easier for you to display the lookup value in a textbox if you could include the look up value in the row source of the combobox.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    19th May 2019 - 07:34 PM