Results 1 to 5 of 5
  1. #1
    New To Access is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    21

    When opening access, action based on a value in a field in certain table

    Hey guys,

    Been wrapping my head around this for days but can't seem to get it right.

    I have a database where i only want one admin to be able to use it at a time, so when an admin open this database it should do a check in the settings table, if it says yes in the answer field it should give a messagebox saying that there is already an admin using the database and then quit. If it says No then it should open the query "UpdateAdminUseToYes" (this changes the No into a Yes) and close this table and open the form Admin_frm.


    Click image for larger version. 

Name:	vdvd.PNG 
Views:	7 
Size:	3.3 KB 
ID:	40374

    I know there is something wrong with my code, cause im nowhere near to having experience in access... Hereunder the code tried to use (btw the settings table is a linked table if that matters)

    Code:
    Dim varx As Variant
    If (DLookup("[answer]", "settings", "Yes")) Then
    MsgBox ("There is already an admin working in this database!")
    DoCmd.Quit
    Else
    DoCmd.OpenQuery "UpdateAdminUseToYes"
    Docmd.close "Bypass_frm"
    Docmd.open "Admin_frm"
    End If
    Thanks for helping me out guys

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    ' get user that opened the db

    Code:
    vUser = Environ("Username")
    bIsAdmin = dlookup("IsManager","tUsers","[userID]='" & vUser & "'")    'are they a manager?
    if bIsManager then
    
    If (DLookup("[answer]", "settings", "Yes")) Then MsgBox ("There is already an admin working in this database!") DoCmd.Quit Else DoCmd.OpenQuery "UpdateAdminUseToYes" Docmd.close "Bypass_frm" Docmd.open "Admin_frm"
    else docmd.openform "frmMainMenu" endif

  3. #3
    New To Access is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    21
    Hi Ranman256,

    The admins open the admin database so upon opening it will allways be an admin, so not sure if what you are suggesting is necessary here

    So i basicly need one line of code that can do a check in the "settings" table and look at the value in the answer field, if it says "Yes" -> give the messagebox and quit, else open the query etc

    So i am just struggling with how to put in VBA to check if the value in that certain field (settings table, answer field) equals Yes or No

    Hope my explanation makes sense

  4. #4
    New To Access is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    21
    Thanks Ranman256!

    Managed to fix my code with the code you suplied, had mixed up the tables and fields etc

  5. #5
    New To Access is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    21
    btw this is what it looks like now if this can help someone else too

    Code:
    Dim varx As Variant
    Dim varx As Variant
    If DLookup("answer", "settings") = "Yes" Then
    MsgBox ("There is already an admin working in this database!")
    DoCmd.Quit
    Else
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "updateadminusetoYes"
    DoCmd.SetWarnings True
    End If
    Last edited by New To Access; 12-04-2019 at 07:24 AM.

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

Similar Threads

  1. opening a certain report based on a field in a form
    By halo123456789 in forum Access
    Replies: 12
    Last Post: 09-10-2019, 07:50 AM
  2. Replies: 3
    Last Post: 04-04-2018, 10:52 AM
  3. Replies: 4
    Last Post: 06-23-2017, 05:17 PM
  4. Replies: 3
    Last Post: 09-05-2016, 10:56 AM
  5. Replies: 1
    Last Post: 02-23-2016, 05:00 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