Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Return a value from a record based on the record's position in a q

Discussion in 'Information Technology' started by Jon22, Jul 28, 2006.

  1. Jon22

    Jon22
    Expand Collapse
    Guest

    I have a form in which I've positioned a whole bunch of unbound text box
    controls to appear like a spread sheet. In the first 'column' of text boxes I
    want to return values from a particular field from a query in the order they
    appear in the datasheet view of this query. That is, in the first Text Box on
    my form I want to return the value from field "x" that appears in the first
    record of this query. In the Text Box directly below this on my form I want
    to return the value from field "x" that appears in the second record of my
    query.

    Is there a function I could use in the Control Source of each text box on my
    form that looks up a value from a field in a query based on the 'record
    number' so to speak?
     
  2. Loading...

    Similar Threads Forum Date
    Sikh News Ontario Police Review Arrest Of Sikh Man Whose Turban Wasn't Returned - CTV News Breaking News Jul 16, 2016
    Movies Ashdoc's short movie review---Tanu weds Manu returns Theatre, Movies & Cinema Jun 6, 2015
    Zoroastrianism Parsis pray for return of the scavenger bird Interfaith Dialogues Aug 28, 2013
    Steve Jobs' Return to Apple Business, Lifestyle & Leisure Mar 28, 2013
    Opinion Campaign to reward homeless man who returned diamond engagement ring hits $175,000 - and that total Breaking News Mar 7, 2013

  3. Maurice

    Maurice
    Expand Collapse
    Guest

    Jon,

    You could try something like this:

    Private Sub Test_Click() -- button or on load from form I used a button..
    Dim rst As ADODB.Recordset
    Dim i As Integer

    Set rst = New ADODB.Recordset

    i = 1
    rst.Open "[your queryname here]", CurrentProject.Connection, adOpenDynamic,
    adLockOptimistic

    With rst
    Do While Not rst.BOF And rst.EOF
    Me.Controls("txtData" & i) = rst(0) ---- this being the first field from
    the query
    rst.MoveNext
    i = i + 1
    Loop
    End With
    rst.Close
    set rst=nothing
    End Sub

    Keep the following in mind;

    You will get an error if the number of records is greater than the number of
    controls on your form.

    Make sure the controls are named "txtData1", "txtData2" etc... (see code)

    Maurice



    "Jon22" wrote:

    > I have a form in which I've positioned a whole bunch of unbound text box
    > controls to appear like a spread sheet. In the first 'column' of text boxes I
    > want to return values from a particular field from a query in the order they
    > appear in the datasheet view of this query. That is, in the first Text Box on
    > my form I want to return the value from field "x" that appears in the first
    > record of this query. In the Text Box directly below this on my form I want
    > to return the value from field "x" that appears in the second record of my
    > query.
    >
    > Is there a function I could use in the Control Source of each text box on my
    > form that looks up a value from a field in a query based on the 'record
    > number' so to speak?
     
  4. Jon22

    Jon22
    Expand Collapse
    Guest

    RE: Return a value from a record based on the record's position in

    Thanks very much for the reply Maurice, but this solution's a bit beyond my
    capabilities I'm afraid. I'm not very profitient with the visual basic (code)
    side of Access. What I mean by that is that I know absolutely nothing of it.

    Jon.

    "Maurice" wrote:

    > Jon,
    >
    > You could try something like this:
    >
    > Private Sub Test_Click() -- button or on load from form I used a button..
    > Dim rst As ADODB.Recordset
    > Dim i As Integer
    >
    > Set rst = New ADODB.Recordset
    >
    > i = 1
    > rst.Open "[your queryname here]", CurrentProject.Connection, adOpenDynamic,
    > adLockOptimistic
    >
    > With rst
    > Do While Not rst.BOF And rst.EOF
    > Me.Controls("txtData" & i) = rst(0) ---- this being the first field from
    > the query
    > rst.MoveNext
    > i = i + 1
    > Loop
    > End With
    > rst.Close
    > set rst=nothing
    > End Sub
    >
    > Keep the following in mind;
    >
    > You will get an error if the number of records is greater than the number of
    > controls on your form.
    >
    > Make sure the controls are named "txtData1", "txtData2" etc... (see code)
    >
    > Maurice
    >
    >
    >
    > "Jon22" wrote:
    >
    > > I have a form in which I've positioned a whole bunch of unbound text box
    > > controls to appear like a spread sheet. In the first 'column' of text boxes I
    > > want to return values from a particular field from a query in the order they
    > > appear in the datasheet view of this query. That is, in the first Text Box on
    > > my form I want to return the value from field "x" that appears in the first
    > > record of this query. In the Text Box directly below this on my form I want
    > > to return the value from field "x" that appears in the second record of my
    > > query.
    > >
    > > Is there a function I could use in the Control Source of each text box on my
    > > form that looks up a value from a field in a query based on the 'record
    > > number' so to speak?
     

Share This Page