Results 1 to 10 of 10
  1. #1
    Star Lounger
    Join Date
    Jun 2001
    Location
    Christchurch, New Zealand
    Posts
    73
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How to retrieve recordset value

    VB's proving to be a good learning curve for me as a non programmer. I've managed to construct the following which retrieves my field data that I want. But now I'm stuck. How do I get the item value to a variable so that I can use it?? A2000

    Function ExtractDates()
    Dim db As Database
    Dim rs As Recordset
    Dim strCriteria As String, strSQL As String

    strSQL = "SELECT tblTrainingDetail.Start FROM tblTraining LEFT JOIN tblTrainingDetail ON tblTraining.ID = tblTrainingDetail.TrainingID; "

    Set db = CurrentDb
    Set rs = db.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

    With rs
    If .RecordCount <> 0 Then
    Do While Not rs.EOF
    x = ????? rs.Fields

    .MoveNext
    Loop
    End If
    End With

    End Function

    Thanks in advance
    Cheers
    Tony

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: How to retrieve recordset value

    I'm not sure what you're trying to do. You're creating a recordset, but it looks like you're only returning a field called Start from tblTrainingDetail. If you have more than one record being returned, then each record will probably have a value for that field. If you explain what you want to do with the values, someone can help you do it, but you can't just store the value to an X variable and then expect to use it for something later, because you're looping through the recordset , which means that the line:

    x = rs!Start

    is going to be executed over and over, once for each record in the recordset. So once you exit the Do Next loop, X will only contain the last value of Start.
    Charlotte

  3. #3
    Star Lounger
    Join Date
    Jun 2001
    Location
    Christchurch, New Zealand
    Posts
    73
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to retrieve recordset value

    Charotte thanks for your reply, x = rs!Start is exactly what I need at this point as I couldn't figure out the code to return this value. Now that I have the value I'm then going use it to build a query.

    The reason I'm doing this is that I have data from one subform that I need to search against data from another subform. and hence multiple critieria. When I tried to use a query to do and set the criteria to the sub form fields it only used the first row of the subform data as the criteria. As a result I was missing matching seachs for any subsequent criteria.

    I couldn't work out any other way to do it and decided to try & use VB to get the criteria from one series of sub form data so that I could use them in the building a query.

    Unless of course there is another way that I should be attacking it?

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: How to retrieve recordset value

    <hr>I have data from one subform that I need to search against data from another subform<hr>
    isn't very clear. You don't normally need to do this, so maybe you should explain what the purpose is. Why are you trying to build a query based on a subform recordset for criteria? For that matter, why are you looking at the recordset at all?

    Are these subforms on different forms? If they're on the same form, it sounds like there may be something wrong with the way the forms are linked. If they're on different forms, what are you trying to compare and to what purpose?
    Charlotte

  5. #5
    Star Lounger
    Join Date
    Jun 2001
    Location
    Christchurch, New Zealand
    Posts
    73
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to retrieve recordset value

    Sorry for my lack of clarity, the following details why I'm wanting to do this.

    The subform data is on different forms, one set of data contains dates that relate to a person being available (start & end) and needs to be able to store more than one date range for each person.

    The second form contains a position that can also have more than one (Start/End) date range attached to it.

    What I then need to do is for each position is to search for an available person/s based on the position date/s that they are available. Each search needs to return any available person for any range of time for a position as well.

    After using a query attached to the subform for criteria I found that it only searched based on the first record in the subform data. Unsure of how to proceed I thought that by building up a function that retrieved each set of critiera from the position details and passing this to a query to search the person table, I would then be able to append each result as it is run into a table that would store my search results. I could then use this to display my results of the search.

    I couldn't think of any other way around this other than the above as I don't think I've stuck this type of thing before or is there another way I should be attacking this.

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: How to retrieve recordset value

    I'm still having trouble with what you're doing. Do you want to populate one subform based on values another? What, if anything, is the link between these two subforms? It sounds to me like your table structure might be at the heart of your problem.
    <hr>one set of data contains dates that relate to a person being available (start & end) and needs to be able to store more than one date range for each person<hr>
    I can't figure out exactly what this means. You don't ordinarily store "available" date ranges for a person, although you store dates when they are not available by creating a record that assigns them to something for that date range. You might *display* available dates, but that is NOT the same thing as storing them.

    You've attempted to explain what you want to do, but tell us why. Often, someone else can come up with a more straightforward method for accomplishing the objective if we know what it actually is.
    Charlotte

  7. #7
    Star Lounger
    Join Date
    Jun 2001
    Location
    Christchurch, New Zealand
    Posts
    73
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to retrieve recordset value

    No I need to use the criteria from one subform to search the records in the second subform. Nothing is linked between either of the forms & subforms.

    I need to store both lots of dates, One set details the required dates for a position and the second contains the people and the dates they are available. Both the positions and the people have to have multiple dates ranges that they are available.

    It would be easy if there was only one range of available dates but there's not I must have both and they can't be fixed fields. This is why I set both set of data up with sub table where I could store the block of dates. The second reason being is that these dates can be changed and alter often and hence need to be very flexible.

    Ok so in plan English, I have people who are available for lost of different date periods (start & end) and a position that is available for lots of date periods (start * end) What I want is a query that retrieves for a position with many dates all the available people regardless of wether they are available for all or one of the dates.

    I have set up a test db (250k) to test it out if that would be any help to anyone?

  8. #8
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: How to retrieve recordset value

    Your explanation is somewhat confusing. As I see it, you have a form that displays information on a specific Position. A subform on that form displays available start/finish times for that position. You want to find all the people who have available start times that match any of the start times in your subform, correct? Given that, then that other form you talked about that that has persons and available times is irrelevant, and shouldn't even be considered.

    It seems to be that all you need is a query that joins the position startingtimes table to person's available time table, based on the starting dates being the same.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  9. #9
    Star Lounger
    Join Date
    Jun 2001
    Location
    Christchurch, New Zealand
    Posts
    73
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to retrieve recordset value

    My apologies for the confusion.

    I want to find all the people who have available start dates that match or that are greater(outside) any of the start dates in the position. (Both forms contain subforms with Start & End Dates. I want to search one subform (People) with the dates as the criteria from the second subform(Position) A many to many search as such given that the position has more than one set of dates and the person can also have more than one set of dates.

    Are you suggesting joining these two tables by the start date field? If I do this it will give me anyone with matching dates but then I wouldn't be able to see anyone with a start date before the positions date.

    Thanks for you help.

  10. #10
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: How to retrieve recordset value

    You need to set a Where condition such that:
    Personnel.StartDate<=Position.StartDate

    Or whatever your table and field name equivalents are.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •