Textbox/Listbox source from Query?

[jwruble]

On the frontend of my database, there is a form where you select what community a member lives in. The community is then stored in the database as a code, not the name of the community.

So now on another section of the form, I am trying to display the community the member is assigned to. This seemed simple enough, however all I have been able to do is display the code # that the community is bound to.

Is there some way to run a query in the background so that the form can grab the matching community name and display it?

 

[CTOROCK]

You could do an if satement like
if StrComm = 13 then
txtcomm.value = "Community Name"
Else etc.
end if

Or if there are too many then what you could do is open the table it is located in and then do a find:


Dim Rst As Recordset
Dim Db As Database
Set Db = CurrentDb
Set Rst = Db.OpenRecordset("Tbl_MasterTable", dbOpenDynaset)

rst.findfirst (StrComm)
txtComm.value = rst.fields("Community&quot.value
rst.close

This will find the record where the number code is and then it will take the community field value and assign it where you like. hope this helps. "The greatest risk, is not taking one."

 

[thornmastr]

Assuming that you have a table of communities and the number you have used matches the key of your community list it should not be a problem to pull the name. For example, if the community number is on your form and you want to see the community name, you would define the community name to be an unbound text box on your form. Iin the default value of that field, put =GetComName() (the = sign is mandatory). Lets assume the number value on your form is called comNbr.

Now, in your form module, you would define GetComName as follows.

Public function GetComName as string: GetComName = “”
Dim db as database
Dim rs as recordset
Dim strsql as string

Strsql = “SELECT COMNAME FROM COMTABLE WHERE COMNBR = “ _
& me.comnbr.value
set db = currentdb
set rs = db.openrecordset(strsql, dbopensnapshot)
if rs.recordcount > 0 then
GetComName = rs!COMNAME
Endif
Rs.close
Set rs = nothing
Set db = nothing
End function
Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com