Results 1 to 13 of 13
  1. #1
    swenger is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2016
    Posts
    151

    Passing information from one table to a record on a form

    I have a table which has 4000 records of unique numbers. I added a field called "used" which is a Yes/No as well as a field called number where each record is numbered from 1 to 4000. I have a form for another table with many records. When I create a new record, the first thing I would like to do is write code which will take the first record which has used set to No from the first table and insert this into my form and then update that record from No to Yes.

    Would someone be able to help me with this?

    Thanks,
    Sam

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Open a recordset on an SQL statement that gets the minimum number where the used field is false. That gets you the number, plus you can use the Edit method to update the record.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    swenger is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2016
    Posts
    151
    I understand the concept. It's just that I am not a programmer and use mostly the wizards.

    I have the statement to return the next number

    SELECT First(MasterIDTable.SN) AS [FirstOfSN]
    FROM MasterIDTable
    WHERE MasterIDTable.Used=No;

    I don't know the syntax to create a Public Function from this to use to store this number as my NextSN

    I declared Public SN as string already. Would like to pass the results of this query to this declared variable NextSN

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    There's no wizard for this, so it's time to get your hands dirty. Here's some info on functions:

    http://www.baldyweb.com/Function.htm

    and recordsets:

    https://msdn.microsoft.com/en-us/lib.../ff820966.aspx
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    swenger is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2016
    Posts
    151
    I am open to this and trying to learn. I tried putting the SQL statement in the Function but don't think Access liked that so I used your second link to set the recordset by calling the query. How do I pass this over?

    Public dbs As DAO.Database
    Public rsQuery As DAO.Recordset

    Public Function FindNextID(strVariable As String) As String

    Set dbs = CurrentDb

    Set rsQuery = dbs.OpenRecordset("qryNextID", dbOpenDynaset)

    End Function

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    VariableName = rsQuery!FirstOfSN

    Then use the Edit method to update the record. It occurs to me that your query may not be editable. If that's the case, use something like

    SELECT TOP 1 SN AS [FirstOfSN]
    FROM MasterIDTable
    WHERE Used=No
    ORDER BY SN
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    swenger is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2016
    Posts
    151
    Correct my query is not editable. I was using it just to return the next ID to use.

    I was able to update my record with your help. It won't however complete the update Query. What perplexes me is that I use the msgbox on a button when I want to test for the variable. when i set msgbox NextID for some reason it comes up empty, yet it is able to record that ID into the field using the Function you helped me with so I don't understand why it comes up blank which would help me understand why my update query isn't working.

    I have the following in a module

    Public NextID As String
    Public dbs As DAO.Database
    Public rsQuery As DAO.Recordset

    Public Function FindNextID()

    Set dbs = CurrentDb
    Set rsQuery = dbs.OpenRecordset("qryNextID", dbOpenDynaset)
    NextID = rsQuery!NextID2

    End Function

    On my form I have the following code behind a button

    Private Sub cmdAssignID_Click()
    FindNextID
    SN = NextID
    DoCmd.OpenQuery "qryUpdateNextID"
    End Sub



    My SQL Statement for the update qry is

    UPDATE MasterIDTable SET MasterIDTable.Used = Yes
    WHERE MasterIDTable.SN=[NextID];

    However it prompts me with a box to fill in the Next ID instead of taking it from the public variable.

  8. #8
    swenger is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2016
    Posts
    151
    Update. The msgbox is displaying the nextID now properly. But update Query is still prompting me to enter info. Am I not able to pass a variable to the query?

  9. #9
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    No, you can't use a variable in a query. You can create a public function that returns the value of the query. That said, I'd do it all with the recordset, using the SQL above.

    rs.Edit
    rs!FieldName = True
    rs.Update
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  10. #10
    swenger is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2016
    Posts
    151
    Thanks for all your help. Can I ask one last thing. I tried the following and it doesn't like the second line

    Public Function UpdateMac()

    rsQuery.Edit
    rsQuery!used = True
    rsQuery.Update

    End Function

  11. #11
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Did you change to use an editable source? Is used a field in that source? I wouldn't make it a second function, I'd do it all in one, and I'd have the objects declared, set and closed in that function.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  12. #12
    swenger is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2016
    Posts
    151
    Thanks for all your help. Worked out.

  13. #13
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Glad you got it working.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 09-19-2016, 06:46 AM
  2. Replies: 1
    Last Post: 04-11-2015, 10:31 AM
  3. Replies: 2
    Last Post: 03-13-2013, 06:13 PM
  4. Replies: 1
    Last Post: 03-28-2012, 04:12 PM
  5. Replies: 5
    Last Post: 04-03-2011, 10:24 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums