Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2009
    Posts
    185

    Unanswered: Lookup count number

    I have a flight db that keep track of rc control airplane flights. I need to get the last cycle number for a battery when entering a new flight but can not figure out how to do this in access.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    is your cycle number ascending numeric
    ..if so select my,column,list, max(cycleno) from mytable where aircraftid=blah
    is your battery usage time satmped
    ...if so select top(1) batteryuse from mytable
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jul 2009
    Posts
    185
    cycle# is ascending numeric in the battery table. when adding a flight I need the cycle # in flight to lookup the cycle# in battery add one to it store than in battery and enter it in flight cycle field.
    Sorry I am very good at Clarion programming but very new to Access and don't understand your answer.

    Quote Originally Posted by healdem
    is your cycle number ascending numeric
    ..if so select my,column,list, max(cycleno) from mytable where aircraftid=blah
    is your battery usage time satmped
    ...if so select top(1) batteryuse from mytable

  4. #4
    Join Date
    Jul 2009
    Posts
    185

    Question

    I think I found the right place for the code. After searching the net for samples I have tried the following. But of course this don't work.
    I did get "voltage = 10" to work but not "cycle = 10" for some reason. Is there a different syntax for a string field vs a number field.
    No one in my town has a book on Access.

    I sure would appreciate the help.

    Private Sub Cycle_BeforeUpdate(Cancel As Integer)
    'Ensure right battery record
    SELECT Battery.ID, Battery.Description FROM Battery;
    'Calc new cycle number
    Cycle = battery.Cycle + 1
    'Updata battery total cycle
    battery.Cycle = Cycle
    '
    End Sub

  5. #5
    Join Date
    Jul 2009
    Posts
    185
    Well I found a better place I am now using before update on the battery
    and cycle to icycle. It appears cycle is a key word. Also I need [] around names with spaces.

  6. #6
    Join Date
    Jul 2009
    Posts
    185
    I got it working needed [] and I add a i,s,d before every field name and each description name are all different. One line of code.
    [battery.ibtotal cycles] = [battery.ibtotal cycles] + 1

    Here is what a friend said I needed to start with I have no idea what it is

    Private Sub Battery___Change()
    Dim con As Object
    Dim rs As Object
    Dim stSql As String
    Dim intOption As Integer

    Set con = Application.CurrentProject.Connection
    stSql = "SELECT * FROM [Battery] where ID =" & Battery__.Text
    Set rs = CreateObject("ADODB.Recordset")
    rs.Open stSql, con, 3, 3
    rs("Total Cycles #") = rs("Total Cycles #") + 1
    Cycles = rs("Total Cycles #")
    rs.Update
    rs.Close
    End Sub

Posting Permissions

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