Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    vicsaccess's Avatar
    vicsaccess is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Apr 2015
    Posts
    451

    need some ideals and input

    i have something differant for me and i'm needing some help how would be the best way to handle it. i have a database for asset management with a main job repair table, each job in the table will also have several entries in a craft table linked to it with keys. so basicly if you are going to change your tire that would be the job and the two individuals doing the work would both have a craft entry for the amount of time and some other details. These are all planned out ahead of time in the planning stage so that we can arrange any contractors or such. now for my need, we have several jobs that are automaticly reaccuring such as changing your oil in the car, these should automaticly populate the job table on their due date. so my plan is to have a table of templates for the jobs and a table for the templates for the crafts again linked by keys. i can put due dates in the templates for the jobs to trigger them but what type of code would i do to insert the job into the repair table and then bring the two or three crafts from the craft template table into the craft table?

  2. #2
    Perceptus's Avatar
    Perceptus is offline Expert
    Windows 7 64bit Access 2007
    Join Date
    Nov 2012
    Location
    Knoxville, Tennessee
    Posts
    659
    Sounds like you are in many to many land.

    you can insert into one table values based on another table.
    Insert Into repairtable (select jobID from jobTable)
    and then have a repair work table to show the work items.
    insert into repairworktable(select joined data from craft table template)

    unify both repair tables with a common key.

  3. #3
    vicsaccess's Avatar
    vicsaccess is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Apr 2015
    Posts
    451
    this will be my first attempt at this, inserting into the table isn't new, its inserting and then inserting several more that are related. my thoughts were
    set a recordset based on the due date being due
    start a loop
    insert first record in job
    start second loop
    insert craft in craft with job template PK
    next craft with PK
    end second loop
    next job
    end first loop

    not used to working with loops yet so i'm just not sure i'm on the right path. i have some samples i'm playing with but no luck so far. all help is appreciated. i'm sure someone is doing this successfully.

  4. #4
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    This isn't exactly what you want, but the method of grabbing the ID from an inserted record to use for the child records will probably come in handy:

    http://allenbrowne.com/ser-57.html
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    vicsaccess's Avatar
    vicsaccess is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Apr 2015
    Posts
    451
    thanks Paul, I'll read it. Not sure if my thought process above is even on the right path. that's really why i need others ideas here. i don't mind doing some research but i hate when i spend a week reading up on something only to find out i should have gone the other direction

  6. #6
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I don't have a problem prepopulating the tables if that's what fits your situation. I have a couple of vehicle maintenance apps I created. I don't prepopulate anything, but I did create reports that advised them of vehicles with scheduled service upcoming (like oil changes). They create the job record when they bring the vehicle in.

    If prepopulating makes sense for you, I'd probably have an automated process run on the appropriate schedule that populated the 2 tables.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    vicsaccess's Avatar
    vicsaccess is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Apr 2015
    Posts
    451
    ok making progress and I've attached a sample test DB with my progress so far. Since this is a little out of my comfort zone I'm asking for some scrutiny since it appears to work but like i said its out of my comfort zone please take a look. Let me go over what I'm trying to accomplish again. I have a DB for asset management but I'll relate it to car maintenance for this to keep it simple. I have a table called circular that has all my cars upcoming maintenance such as changing the oil or rebuilding the motor. Table circularcraft tells me how many mechanics will be needed to change the oil or rebuild the motor with a entry for each mechanic and description of the task they will accomplish. Each job can have one mechanic or four so they are all different depending on how difficult the job is in the circular table. when complete each job in circular will be triggered by its due date but for now its triggered from a text box that i enter the job number in and use the command button to start. so looking at the second job in circular it has four related crafts in circularcraft and all these need to be moved into the workque and workquecraft tables so they can be viewed by the mechanics to do the job. please take a look, make suggestions and let me know your thoughts.
    Attached Files Attached Files

  8. #8
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I have a couple of comments about the code:
    The top two lines of every code module should be
    Code:
    Option Compare Database
    Option Explicit

    I would change the declaration for dB to
    Code:
    Dim db As DAO.Database

    You are not using the "With" keyword correctly. If you use "WITH rs1", you don't need to keep referring to "RS1". I cut out some code to make it easier to see...... when using the "WITH" keyword, your code should look something like
    Code:
        With rs1
    
            .MoveFirst
            Do Until .EOF
                CurrentDb.Execute (" INSERT INTO workquet( circularid,jobid, detail,duedate ) " & _
                                   " VALUES (""" & !circularid & """,""" & !jobid & """,""" & !detail & """,""" & !duedate & """ ) ")
    
                .MoveNext
            Loop
            .Close
        End With
    You might want to clean up:
    Code:
        Set rs2 = Nothing
        Set rs1 = Nothing
        Set db = Nothing
    
    End Sub
    You don't check if there are records in the recordset before continuing the code. You will have a debug dialog box displayed if there aren't records in the recordset.
    Happened to me when I thought "There will always be records"...... and there weren't. Boy, was I .
    I use "If Not rs1.bof and Not rs1.eof Then" .....

  9. #9
    vicsaccess's Avatar
    vicsaccess is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Apr 2015
    Posts
    451
    thanks Steve, that's exactly the input i'm looking for. made changes and its still working. more progress

  10. #10
    vicsaccess's Avatar
    vicsaccess is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Apr 2015
    Posts
    451
    ok it was working well enough that i moved it out of the test environment and trying to incorporate it into the database with the rest of the code that goes with it but I'm hitting some blocks that i need help on. first problem is after i insert the main job from the circular table into the workque table i reset the due date for the next cycle. with this type of recordset how do i write the where clause of the update to be circularid = current recordset circularid? second problem so far is that my variable "x" that is picking up the new PK to insert as a FK in the workquecraft table is not resetting. all my records going into workquecraft are getting the same FK even though the cursor has moved onto a new record. can it be reset at each loop?


    Code:
    Private Function circular()
        Dim db As DAO.Database
        Dim rs1 As DAO.Recordset '''circular table'''
        Dim rs2 As DAO.Recordset '''circular craft table'''
        Dim sqlrs1 As String '''circular sql'''
        Dim sqlrs2 As String '''circular craft sql'''
        Dim x As Integer
        Dim n As Date
        
        sqlrs1 = "SELECT * FROM circularT" '''get circular table'''
        sqlrs2 = "SELECT * FROM circularcraftT" '''get circular craft table'''
        
        Set db = CurrentDb
        Set rs1 = db.OpenRecordset(sqlrs1)
        Set rs2 = db.OpenRecordset(sqlrs2)
        
        With rs1 '''loop thru circulart'''
        If Not .BOF And Not .EOF Then '''check to see if on a record'''
        .MoveFirst
        Do Until .EOF
        If (!DueDate - Date) <= 0 Then '''check due dates that are due today or past'''
            '''insert main job into record'''
            CurrentDb.Execute ("INSERT INTO workqueT(circularid, locid, deptid, systemid, assetid, componentid,workid,summary,detail,statusid) " & _
            "VALUES(""" & !circularid & """,""" & !LocID & """,""" & !DeptID & """,""" & !SystemID & """,""" & !AssetID & """,""" & _
             !ComponentID & """,""" & !WorkID & """,""" & !Summary & """,""" & !detail & """,'" & 1 & "') ")
            
            n = !DueDate + 5 '''set new due date'''
            CurrentDb.Execute ("UPDATE circularT SET duedate=#" & Format(n, "mm/dd/yyyy") & "#  WHERE circularid=!circularid ")
            
            x = CurrentDb.OpenRecordset("select @@identity")(0) '''get unique ID'''
            
            With rs2 '''loop thru circularcraftt'''
            If Not .BOF And Not .EOF Then
            .MoveFirst
            Do Until .EOF
            '''insert the crafts for the main jobs'''
            CurrentDb.Execute ("INSERT INTO WorkquecraftT(workqueID,craftid,hours,craftnotes,statusid) " & _
            "VALUES (""" & x & """,""" & !CraftID & """,""" & !Hours & """,""" & !CraftNotes & """,'" & 5 & "') ")
            .MoveNext
            Loop
            .Close
            End If
            End With
            
            
        End If
        
        .MoveNext
        Loop
        .Close
        End If
        End With
        
        Set rs2 = Nothing
        Set rs1 = Nothing
        Set db = Nothing
        
    End Function
    thanks, all help is appreciated.

  11. #11
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    It appears that your BE is SQL Server (or equivalent)?
    So I cannot test any of this.....


    how do i write the where clause of the update to be circularid = current recordset circularid?
    You need to concatenate "!circularid". You have
    Code:
    CurrentDb.Execute ("UPDATE circularT SET duedate=#" & Format(n, "mm/dd/yyyy") & "#  WHERE circularid=!circularid ")
    Should be
    Code:
    CurrentDb.Execute ("UPDATE circularT SET duedate=#" & Format(n, "mm/dd/yyyy") & "#  WHERE circularid= " & !circularid )
    Why are you using "Format(n, "mm/dd/yyyy")"? This converts the date to a string. Don't you want a Date??



    second problem so far is that my variable "x" that is picking up the new PK to insert as a FK in the workquecraft table is not resetting. all my records going into workquecraft are getting the same FK even though the cursor has moved onto a new record. can it be reset at each loop?
    I think there are two problems here.
    1) You have declared "x" to be an Integer. An ACCESS integer. Not the same and an SQL INT.
    I found this:
    SQL
    Int -2,147,483,648 to 2,147,483,647

    Access
    Integer -32,768 and 32,767 (Allows whole numbers between )
    Long -2,147,483,648 and 2,147,483,647 (Allows whole numbers between)

    So I changed the code to
    Code:
       Dim x As Long
    And I found this
    Code:
     Dim query As String
      Dim newRow As Long  ' note change of data type
      Dim db As DAO.Database
    
      query = "INSERT INTO InvoiceNumbers (date) VALUES (" & NOW() & ");"
      Set db = CurrentDB
      db.Execute(query)
      newRow = db.OpenRecordset("SELECT @@IDENTITY")(0)
      Set db = Nothing

    Then I think the line to get the new PK should be moved up to the first INSERT query. You have it after the UPDATE query.



    Now I have a question.
    In both Insert queries, why is every value delimited with quotes??? I would have thought that at least the ID fields would be numeric.


    Here is the code as I have modified it. Remember, I have not tested these changes
    Code:
    Private Sub circular()
        Dim db As DAO.Database
        Dim rs1 As DAO.Recordset    '''circular table'''
        Dim rs2 As DAO.Recordset    '''circular craft table'''
        Dim sqlrs1 As String    '''circular sql'''
        Dim sqlrs2 As String    '''circular craft sql'''
        Dim sSQL As String
        Dim NewIdent As Long   ' < was -   Dim x As Integer
        Dim dNewDueDt As Date
    
        sqlrs1 = "SELECT * FROM circularT"    '''get circular table'''
        sqlrs2 = "SELECT * FROM circularcraftT"    '''get circular craft table'''
    
        Set db = CurrentDb
        Set rs1 = db.OpenRecordset(sqlrs1)
        Set rs2 = db.OpenRecordset(sqlrs2)
    
        With rs1    '''loop thru circulart'''
            If Not .BOF And Not .EOF Then    '''check to see if on a record'''
                .MoveFirst
                Do Until .EOF
    
                    dNewDueDt = Empty   'clear new due date
                    NewIdent = 0         'reset new identity
    
                    If (!DueDate - Date) <= 0 Then    '''check due dates that are due today or past'''
                        '''insert main job into record'''
    
                        sSQL = "INSERT INTO workqueT(circularid, locid, deptid, systemid, assetid, componentid,workid,summary,detail,statusid)"
                        sSQL = sSQL & " VALUES(""" & !circularid & """,""" & !LocID & """,""" & !DeptID & """,""" & !SystemID & """,""" & !AssetID & ""","""
                        sSQL = sSQL & !ComponentID & """,""" & !WorkID & """,""" & !Summary & """,""" & !detail & """,'" & 1 & "')"
                        '        Debug.Print sSQL   'for debugging
                        db.Execute sSQL, dbFailOnError
                        NewIdent = CurrentDb.OpenRecordset("select @@identity")(0)    '''get unique ID'''
    
                        dNewDueDt = !DueDate + 5    '''set new due date'''
    
                        sSQL = "UPDATE circularT SET duedate=#" & Format(dNewDueDt, "mm/dd/yyyy") & "#  WHERE circularid = " & !circularid
                        '        Debug.Print sSQL      'for debugging
                        db.Execute sSQL, dbFailOnError
    
    
                        With rs2    '''loop thru circularcraftt'''
                            If Not .BOF And Not .EOF Then
                                .MoveFirst
                                Do Until .EOF
                                    '''insert the crafts for the main jobs'''
    
                                    sSQL = "INSERT INTO WorkquecraftT(workqueID,craftid,hours,craftnotes,statusid)"
                                    sSQL = sSQL & " VALUES (""" & NewIdent & """,""" & !CraftID & """,""" & !Hours & """,""" & !CraftNotes & """,'" & 5 & "')"
                                    '        Debug.Print sSQL       'for debugging
                                    db.Execute sSQL, dbFailOnError
    
                                    .MoveNext
                                Loop
                                .Close
                            End If
                        End With
    
    
                    End If
    
                    .MoveNext
                Loop
                .Close
            End If
        End With
    
        Set rs2 = Nothing
        Set rs1 = Nothing
        Set db = Nothing
    
    End Sub
    I added the Debug statements because I like to look at the SQL to see if it is properly formed. (spaces, delimiters)
    I changed "x" and "n" because.... well , they are poor names/not descriptive.
    Last edited by ssanfu; 03-02-2016 at 07:29 PM.

  12. #12
    vicsaccess's Avatar
    vicsaccess is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Apr 2015
    Posts
    451
    thanks, not using SQL server just access and trying to learn VBA as i go. i'll look closely at your recommendations tomorrow. why do you think SQL server?

  13. #13
    vicsaccess's Avatar
    vicsaccess is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Apr 2015
    Posts
    451
    Thanks, i looked at it and made several changes. the suggested where clause of my first problem worked, sorry this is my first attempt at using recordsets like this, much less two at a time. as for the second problem, the more i stepped thru it and then looked at the new records it looks like my problem is not my PK but rather its just coping all the records from RS1 and giving them the new PK. what is the best way to limit the insert SQL in RS2 to only records with the circularid matching the circularid from RS1? also fixed all my delimiters (being lazy, it worked and i kept going) and as for the formatting date. i originally tried set duedate=#" & n & " # but could not get that to work and the format was the best i could come up with. so for now i guess my biggest question is how do i restrict rs2 to be only records inserted where the circularid from rs1 matches circularid from rs2?

  14. #14
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    why do you think SQL server?
    Because if this line:
    Code:
    CurrentDb.OpenRecordset("select @@identity")(0)    '''get unique ID'''
    That is SQL Server syntax. I have never seen it used in Access. But I am using A2010.....

  15. #15
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    As far as I know @@Identity works in JET (mdb). It certainly works with ACE (accdb), just tested in 2007.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 09-23-2015, 02:29 PM
  2. Replies: 1
    Last Post: 11-04-2014, 12:07 PM
  3. Replies: 6
    Last Post: 10-27-2014, 08:05 PM
  4. Replies: 5
    Last Post: 01-29-2014, 02:42 PM
  5. Input Forms - How To Input Multiple Fields/Records?
    By butterbescotch in forum Forms
    Replies: 1
    Last Post: 04-04-2013, 06:30 AM

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