Page 2 of 2 FirstFirst 12
Results 16 to 26 of 26
  1. #16
    ShaunG is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Jan 2018
    Posts
    237

    cheers for hints

    Minty just check but when the combo boxes are empty doesnt prompt with the message to select something, ill have a sus tomorrow i need sleep lol.... let me know if ya think of anything, cheers for your help.

  2. #17
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    PMFJI,

    You have the delete command in the wrong place.
    If you select "NO" to the question "Are you sure you want to Delete?.", what happens? You drop out of the SELECT CASE construct , then the delete code executes.

    Here is how I would write the code (modifying Minty's code )
    Code:
    Private Sub cmdDeleteJoinery_Click()
        Dim iAccLvl As Integer
        Dim Response As Integer        *** 'Note that ALL declarations are together at the top of the routine!! ***
        Dim delJoinery As Integer
    
        iAccLvl = DLookup("[AccessLevel]", "tblEmployees", "[EmployeeID_PK] = " & Forms!LoginForm!cboUser)
    
        Select Case iAccLvl
            Case 1, 2, 3
                Response = MsgBox(prompt:="Are you sure you want to Delete?.", Buttons:=vbYesNo)
                If Response = vbNo Then     'no selected
                    ' do nothing
                Else                        'yes selected
                    ' do something
                    If IsNull(Me.cboJoinery) Then
                        MsgBox "Please select Joinery Unit"
                        Me.cboJoinery.SetFocus
                        Exit Sub
                    Else
                        delJoinery = "DELETE * FROM tblJoineryUnit WHERE JoineryID_PK = " & Me.cboJoinery & ";"
                        '  Debug.Print delJoinery
                        CurrentDb.Execute delJoinery, dbFailOnError
                        
                        'now requery the combo box
                        Me.cboJoinery.Requery
                        Me.cboJoinery = Me.cboJoinery.ItemData(0)
                    End If
                End If
            Case Else
                MsgBox "You Do Not have Access", vbOKOnly
        End Select
    End Sub

    You should be able to step through the code in your mind to see if the code does what you want.
    If you have problems (I do - a lot), then you need to single step through the code.

    For debugging suggestions, see Debugging VBA Code


    Good luck with your project.......

  3. #18
    ShaunG is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Jan 2018
    Posts
    237
    Quote Originally Posted by ssanfu View Post
    PMFJI,

    You have the delete command in the wrong place.
    If you select "NO" to the question "Are you sure you want to Delete?.", what happens? You drop out of the SELECT CASE construct , then the delete code executes.

    Here is how I would write the code (modifying Minty's code )
    Code:
    Private Sub cmdDeleteJoinery_Click()
        Dim iAccLvl As Integer
        Dim Response As Integer        *** 'Note that ALL declarations are together at the top of the routine!! ***
        Dim delJoinery As Integer
    
        iAccLvl = DLookup("[AccessLevel]", "tblEmployees", "[EmployeeID_PK] = " & Forms!LoginForm!cboUser)
    
        Select Case iAccLvl
            Case 1, 2, 3
                Response = MsgBox(prompt:="Are you sure you want to Delete?.", Buttons:=vbYesNo)
                If Response = vbNo Then     'no selected
                    ' do nothing
                Else                        'yes selected
                    ' do something
                    If IsNull(Me.cboJoinery) Then
                        MsgBox "Please select Joinery Unit"
                        Me.cboJoinery.SetFocus
                        Exit Sub
                    Else
                        delJoinery = "DELETE * FROM tblJoineryUnit WHERE JoineryID_PK = " & Me.cboJoinery & ";"
                        '  Debug.Print delJoinery
                        CurrentDb.Execute delJoinery, dbFailOnError
                        
                        'now requery the combo box
                        Me.cboJoinery.Requery
                        Me.cboJoinery = Me.cboJoinery.ItemData(0)
                    End If
                End If
            Case Else
                MsgBox "You Do Not have Access", vbOKOnly
        End Select
    End Sub

    You should be able to step through the code in your mind to see if the code does what you want.
    If you have problems (I do - a lot), then you need to single step through the code.

    For debugging suggestions, see Debugging VBA Code


    Good luck with your project.......
    thanks for that ssanfu.

    yeah i've only just got my head a little around declarations and how to use them, can you explain this part for me -

    ' Debug.Print delJoinery
    CurrentDb.Execute delJoinery, dbFailOnError

  4. #19
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Code:
    '  Debug.Print delJoinery 
    If you un-comment the line above, the value of the variable "delJoinery" will be printed in the immediate window.


    Code:
    CurrentDb.Execute delJoinery, dbFailOnError
    "CurrentDb.Execute" executes an action query, WITHOUT having to set "Warnings Off" and "Warnings On" (I never use DoCmd.RunSQL).
    (see help or Goggle)

    Action queries are queries that can add, change, or delete (as in Append, Update, Delete) multiple records at one time.

  5. #20
    ShaunG is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Jan 2018
    Posts
    237
    cheers ssanfu for explaining.


    I wonder if you guys can help me with this one, with using the codes above ive put in a hyperlink to open an existing database.

    I managed to get it working great thanks to Minty and Ssanfu, both database are pretty much replica's. i'm wanting to take the user name and password from the initial db and when the 2nd one is open transfer the details so that person is logged in and even if i can manage to open at the record i want to open, this is my code so far -

    Code:
    Private Sub cmdReceivalOfGoods_Click()    Dim iAccLvl As Integer
        Dim Response As Integer        'Note that ALL declarations are together at the top of the routine!!
        Dim delStage As String
    
    
        iAccLvl = DLookup("[AccessLevel]", "tblEmployees", "[EmployeeID_PK] = " & Forms!LoginForm!cboUser)
    
    
        Select Case iAccLvl
            Case 1, 2, 3, 4
                Response = MsgBox(prompt:="Does the Order Number Have 'CT' at the start?.", Buttons:=vbYesNo)
                If Response = vbNo Then     'no selected
                    DoCmd.OpenForm "ReceivedGoods"
                    Exit Sub
                Else                        'yes selected
                    ' do something
                    Response = MsgBox(prompt:="This Order Number is in the Old database, do you wish to Open the Old Database?.", Buttons:=vbYesNo)
                        If Response = vbYes Then
                            Me.txtHyperLink2.Hyperlink.Follow
                            DoCmd.Quit
                         Else
                            ' Do Nothing
                        Exit Sub
                        
                    End If
                End If
            Case Else
                MsgBox "You Do not have Permissions", vbOKOnly
        End Select
    End Sub

  6. #21
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Simply link the tables from the old database and build a form to query the old records.
    Hopping in and out of databases is not very efficient, and probably in this case not required.

    Keep things simple.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  7. #22
    ShaunG is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Jan 2018
    Posts
    237
    Quote Originally Posted by Minty View Post
    Simply link the tables from the old database and build a form to query the old records.
    Hopping in and out of databases is not very efficient, and probably in this case not required.

    Keep things simple.

    I know and can tell the way i have it set up is not to efficient at the moment especially with hyperlinks, but that seems like a lot of work to do create forms to go between dbs as the way orders are placed on the 2 db's are different this is why i started a fresh start and I just wanted to tap into the old db when needed. hoping in 2-3 months the old db would only be used to look up material used for jobs if needed.

    so your saying have the new tables linked and also old tables linked? then i would be doubling up,

    apologies if im not getting this, im trying to understand the concept of what your saying and how to put into practice

  8. #23
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Lets take a step back and get the full picture here.
    Say you have DatabaseOld with a table called Jobs, do you have the same table / structure and table name in DatabaseNew?
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  9. #24
    ShaunG is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Jan 2018
    Posts
    237
    Quote Originally Posted by Minty View Post
    Lets take a step back and get the full picture here.
    Say you have DatabaseOld with a table called Jobs, do you have the same table / structure and table name in DatabaseNew?

    my tables are similar and same names but new db has a few new fields in some of the tables.

    there are 2 extra tables which have edited my relationship from the old db.....

    old db - tbljobs relationship to tblJoinery relationship to tblOrders relationship to tblOrderDetails relationship to tblOrderNumbers = 1 to many relationships...... which i changed to

    New db - tbljobs relationship to tblStage_BuildingName relationship to tblAddress relationship to tblOrderDetails relationship to tblOrderNumbers = 1 to many relationships

  10. #25
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    How often do you envisage referring to the old data? This all comes down to time/effort/usage sums on your behalf really.

    if it's not often, I would simply open the old database and make it read only, I wouldn't quit out the new database, you'll probably still need users in it.
    Change the old db forms slightly (nasty big red OLD labels ) to make it obvious that it's the old system.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  11. #26
    ShaunG is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Jan 2018
    Posts
    237
    down the track give 6-7 months not much, would be for a select few of 3-4 people to look up an old job to see what material was used previosly if we need to match it.

    the new database contains all jobs we haven't ordered any material for which means no Joinery units have been recorded. so until the jobs that have stuff ordered for finish guys will have order more material through the old db this is a little cumbersum but only for a period until we ween out the finished jobs.

    i have prep the guys so i think its best just to leave and as you said put some big labels on it clearly marking OLD DB.

    by the way i have changed all my if statements through out to the codes you and ssanfu helped me with and all works bloody awesome!!!

    cheers for the help

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

Similar Threads

  1. Replies: 5
    Last Post: 04-16-2018, 10:51 AM
  2. Replies: 1
    Last Post: 10-01-2015, 10:02 AM
  3. Multiple iif statements
    By rlsublime in forum Queries
    Replies: 3
    Last Post: 04-25-2012, 10:33 AM
  4. Multiple SQL Statements
    By springboardjg in forum Queries
    Replies: 1
    Last Post: 04-18-2011, 10:32 AM
  5. Replies: 3
    Last Post: 10-13-2010, 03:35 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