469,125 Members | 1,712 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,125 developers. It's quick & easy.

Extracting the value from a field each record of table to populate a list

Hello everybody
I am struck at point while developing my patient database. I am sure it will be pretty simple thing but I am struck.
I come to my question. I have a table that stores the name of Investigations that I choose for my patients. I want to populate a list of selected investigations via a loop but I could not. Here is the code I made so far.
Expand|Select|Wrap|Line Numbers
  1. Function PostOpInvestigations() As String
  2. Dim msgStr As String, rstPostOpInvest as DAO.recordset
  3.  Set rstPostOpInvest = CurrentDb.OpenRecordset("SELECT ID, InvestName FROM tblInvestigations WHERE Selection = True")
  4. If rstPostOpInvest.RecordCount <> 0 Then
  5.  rstPostOpInvest.MoveFirst
  6.   msgStr = rstPostOpInvest.InvestName
  7.   Do While Not rstPostOpInvest.EOF
  8.   rstPostOpInvest.MoveNext
  9.   Loop
  10.    msgStr = msgStr & vbCrLf & msgStr
  11. End If
  12. PostOpInvestigations = msgStr
  13. End Function
when I run the code, it displays only one investigation twice. i want that all the selected investigations be displayed.
Please help me out with this code or advise me some other one.
Jan 17 '14 #1
2 828
Rabbit
12,516 Expert Mod 8TB
Line 10 needs to be inside the loop. And also you need to append the value from the recordset, right now you're appending itself to itself.
Jan 17 '14 #2
@Rabbit
Thanks Rabbit
I changed code as under and it works fine
Expand|Select|Wrap|Line Numbers
  1. Function PostOpInvestigations() As String
  2. Dim msgStr As String, rstPostOpInvest as DAO.recordset
  3.  Set rstPostOpInvest = CurrentDb.OpenRecordset("SELECT ID, InvestName FROM tblInvestigations WHERE Selection = True")
  4. If rstPostOpInvest.RecordCount <> 0 Then
  5.  rstPostOpInvest.MoveFirst
  6.   Do While Not rstPostOpInvest.EOF
  7.    msgStr = msgStr & vbCrLf & rstPostOpInvest.InvestName
  8.   rstPostOpInvest.MoveNext
  9.   Loop
  10. End If
  11. PostOpInvestigations = msgStr
  12. End Function
Jan 17 '14 #3

Post your reply

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

Similar topics

7 posts views Thread by Marco Simone | last post: by
2 posts views Thread by Remco Groot Beumer | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.