Combo box values from a query using field name as a condition

[normm]

Hi all hopefully someone can help me with this, I think it should be possible but I'm not sure how to do it, my access experience is limited.

If this is a question that has been answered somewhere else a link to a guide would be appreciated however I cannot currently find one.

I have a very simple Access 2007 database consisting of 2 tables, one table to recieve the answers to a questionaire and one to store values used by combo controls on the input form.

The lookup table consists of a unique ID, an intended target table name, a field name mirroring the field name in the target table and a text description to be featured in the combo box.

I intend to use the following query for each combo box to supply the appropriate values.

select ID, description 
from lookuptable 
where tablename = "**TARGET TABLE NAME**" and 
fieldname = "**TARGET FIELD NAME**"

Is there a way to detect **TARGET TABLE NAME** and **TARGET FIELD NAME** from the current form to save me doing this manually each time??

thanks in advance for any help you can give me with this.

Normm

 

[PHV]

Can't you build the SQL string in VBA ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[normm]

Sorry I possibly should have said I am purely a copy and paste VBA programmer and I generally guess how to do these kinda things and google it.

If that would be the way to do it I would appreciate direction to a tutorial, especially the referencing the field element bits.

I'm guessing from your response that I should be able to set multiple combo box sources in onload and on current events? possibly using me.'fieldname' in the where query but to be honest thats about as far as my access knowlage goes.

I generally write SQL code and web pages but every now and then someone gives me an access form to develop and I muddle through it by googling. I couldn't find what I'm looking for - hey if you could suggest better search terms that would lead me to the answer then I would really appreciate that.
I've been using

"access 2007 field name in where clause for combo" and similar

any further help massively appreciated.

Normm

 

[PHV]

a way to detect **TARGET TABLE NAME** and **TARGET FIELD NAME** from the current form
Doe's the current form have controls holding those values ?
If yes the a starting point:

strSQL = "select ID, description from lookuptable" _
 & " where tablename='" & Me![**TARGET TABLE NAME** control] & "'" _
 & " and fieldname='" & Me![**TARGET FIELD NAME** control] & "'"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MajP]

You could store the table name and field value in the control's tag property. Like this

yourtableName;yourFieldName

Do not put this in "quotes"


then in the enter event of the control
=getRowSource()

Public Function getRowSource() As String
On Error GoTo errlbl
Dim aTag() As String

aTag = Split(Screen.ActiveControl.Tag, ";")
getRowSource = "select ID, description from lookuptable where tablename='" & aTag(0) & "' "
getRowSource = getRowSource & " AND fieldName = '" & aTag(1) & "'"
Screen.ActiveControl.RowSource = getRowSource
Exit Function
errlbl:
If Err.Number = 2474 Then
Exit Function
Else
MsgBox Err.Number & " " & Err.Description
End If
End Function