473,324 Members | 2,535 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

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 1008
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

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

Similar topics

3
by: L. D. James | last post by:
Can someone help me to define a format of a database, or a table that has a field that will have tables in that field? Please look at the example and explanation below: Table: Roster ...
7
by: Marco Simone | last post by:
Hi, What is your opinion about using Lookup field in table. I would like to use lookup field in table 1, so that I can choose data in combo box from table 2. Is this good design of database? ...
2
by: Remco Groot Beumer | last post by:
Hello, I created a program in which I use modules and classmodules for setting my variables. For example when I need to set the customerID in a variable I use something like: ...
4
by: Onion | last post by:
This has to be simple, but I'm forced to admit that I'm a novice who can't figure it out. I have a listbox in a form that allows multiple selections. That works fine. The problem: I can't...
4
by: justin tyme | last post by:
Hello Experts! I would like to combine (which may not be the correct technical term) two text fields from the same table in a query. Specifically, text field A and text field B are both lists of...
11
by: redantho | last post by:
As a relative beginner with Access/VBA (2003 version), I am looking for a solution to what seems to be a fairly simple problem... When a user inputs a text string in a form object, I would like...
2
by: mukesh | last post by:
Can we use expression in default value for a table field for example – IIf(Table-1.field-1=table-2 . field-1, table-1.field-2, 0) Interpretation – If field-1 of table -1 is like/equal to...
5
by: Rex | last post by:
Hi, I want to change a value in one table depending on the value(s) in another table. I am trying to achieve this in a form. to elaborate I have a many-to-many relationship between tables...
2
by: Hillwalker | last post by:
Hi Raw recruit, no VB knowledge but some general programming experience..... Access 2000 on XP In form view, I wish to fill the field (ONLY on the record being viewed) with the maximum value...
0
by: troy howard | last post by:
Good evening, I am trying compare a value from one table to another, and have an new field populate. I am using a Case Statement in order to try to accomplish this task. The error is: Msg 156,...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.