Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    hinchi1 is offline Competent Performer
    Windows 10 Access 2013 32bit
    Join Date
    Sep 2017
    Posts
    181

    Desk Count


    I have designed a database and one of the forms is to be able to add desk numbers that are located in different rooms. Using DLookUp, how do i compare 2 fields to see if the desknumber entered in the form for a given room number already exist? I have used DLookUp before but to only to check against one field in a table. Thanks in advance for you support.

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Use
    Code:
     if  Dcount(desk parameters) >0 then  'that desk number exists
    .....

  3. #3
    hinchi1 is offline Competent Performer
    Windows 10 Access 2013 32bit
    Join Date
    Sep 2017
    Posts
    181
    not too sure about DCount, could you give example of how code may look please

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    As orange has already said, something like this will tell you if it exists already
    Code:
    If DCount("DeskNumber","TableName","RoomNumber = some value")>0 Then
    If DCount=0, its not been used already
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    DCount() has the same syntax as DLookup(), plus Orange linked to a reference in the response.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    Join Date
    Apr 2017
    Posts
    1,673
    How do you need to count desks in room? To prevent adding a desk into room when it was added earlier?

    When this is the case, then there is a better solution. With correct database design it is simply not possible!

  7. #7
    hinchi1 is offline Competent Performer
    Windows 10 Access 2013 32bit
    Join Date
    Sep 2017
    Posts
    181
    Quote Originally Posted by ArviLaanemets View Post
    How do you need to count desks in room? To prevent adding a desk into room when it was added earlier?

    When this is the case, then there is a better solution. With correct database design it is simply not possible!
    Hi, I just need code or function to check if desk number has already been entered in the room. I have used DlookUp to check one field in my table, but for this to work. I think I need to compare the room number field against the desk number field. Unless there is another way to do that.

  8. #8
    Join Date
    Apr 2017
    Posts
    1,673
    You need an unique index for room number and desk number at current moment.

    I'm trying to explain:
    You have rooms defined by RoomNo (probably you have a table where all rooms are listed, with RoomNo as PK, but it isn't a must);
    You have items (desks) defined by ItemNo/DeskNo (and probably you have a table where all items are listed too, with ItemNo as primary key);
    To keep track, which item is placed in which room, you must have a table like
    tblRoomItems: RI_ID, RoomNo, ItemNo, ..., where RI_ID is an autonumeric Primary Key. You define an additional unique index composed from fields RoomNo and ItemNo. As a result, you cantt enter the same ItemNo and RoomNo combination twice into table tblRoomItems;
    The above isn't an end jet. Such design doesn't restrict to insert the same ItemNo with different RoomNo. In reality, you even need to have apossibility to do this, as the same desk in past was in another room. But you have to restrict entries, where the item is in different rooms at same time!
    So you need to add to table tblRoomItems additional fields to register the time interval the item was assigned to room. And one field more, to mark the record with current location of item.
    tblRoomItems: RI_ID, RoomNo, ItemNo, AssignStartDate, AssignEndDate, ...
    AssignStartDate must be always Not Null, but AssignEndDate may be empty. You create an unique index composed from field ItemNo, and expression Nz(CurrentLocation,Date()), which doesn't allow you to enter another record with missing AssignEndDate for same item, or with same end date in different locations, an unique index composed from fields ItemNo, RoomNo, AssignStartDate, which doesn't allow to assign the same item into different rooms at same time.
    From here on it will get more difficult. You'll need a code in Afterupdate events of AssignStartDate and AssignEndDate fields to avoid other situations, where entered or updated date will contradict with dates for sqame item in another records.

  9. #9
    hinchi1 is offline Competent Performer
    Windows 10 Access 2013 32bit
    Join Date
    Sep 2017
    Posts
    181
    I have used this code below to to trap duplicate desk numbers for a given room: When i tab out of the desknumber textbox on the form i get the following error
    The RoomID in my table is a "Number" and the DeskNumber is "short text" as some desk numbers could be "D3", "E1", etc. Maybe i am just going in the wrong direction.


    Run Time Error '3464'
    Datatype mismatch in criteria expression:

    If Me.cboRooms = DLookup("[RoomID]", "tblDeskInformation", stLinkCriteria) Then

    "Private Sub txtDeskNumber_AfterUpdate()"

    Dim NewRoom, NewDesk As String
    Dim stLinkCriteria As String
    Dim DeskNumber As Integer


    'Assign the entered room number and desk number
    NewRoom = Me.cboRooms.Value
    NewDesk = Me.txtDeskNumber.Value
    stLinkCriteria = "[RoomID] = " & "'" & NewRoom & "' and [DeskNumber] = " & "'" & NewDesk & "'"
    If Me.cboRooms = DLookup("[RoomID]", "tblDeskInformation", stLinkCriteria) Then


    MsgBox "This room, " & NewRoom & ", has already been entered in database." _
    & vbCr & vbCr & "with DeskNumber " & NewDesk & "" _
    & vbCr & vbCr & "Please check customer name and address again.", vbInformation, "Duplicate information"
    Me.Undo 'undo the process and clear all fields
    'show the record of matched room number and desk number from the desk information table
    DeskNumber = DLookup("[RoomID]", "tblDeskInformation", stLinkCriteria)
    Me.DataEntry = False
    DoCmd.FindRecord DeskNumber, , , , , acCurrent
    End If
    *End Sub*

  10. #10
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Desk number is an integer but you have used text delimiters
    Assuming room 'number' is in fact text e.g. R1, this should work

    Code:
    Private Sub txtDeskNumber_AfterUpdate()
        
    Dim NewRoom As String, NewDesk As Integer
    Dim stLinkCriteria As String
    Dim DeskNumber As Integer
    
    'Assign the entered room number and desk number
    NewRoom = Me.cboRooms
    NewDesk = Me.txtDeskNumber
    
    stLinkCriteria = "RoomID = '" & NewRoom & "' And DeskNumber =" & NewDesk
    
    If Me.cboRooms = DLookup("[RoomID]", "tblDeskInformation", stLinkCriteria) Then
    MsgBox "This room, " & NewRoom & ", has already been entered in database." _
    & vbCr & vbCr & "with DeskNumber " & NewDesk & "" _
    & vbCr & vbCr & "Please check customer name and address again.", vbInformation, "Duplicate information"
    Me.Undo 'undo the process and clear all fields
    'show the record of matched room number and desk number from the desk information table DeskNumber = DLookup("RoomID", "tblDeskInformation", stLinkCriteria) Me.DataEntry = False DoCmd.FindRecord DeskNumber, , , , , acCurrent '<== do you need acCurrent here?
    End If End Sub
    If room number is also a number then use:
    stLinkCriteria = "RoomID = " & NewRoom & " And DeskNumber =" & NewDesk

    NOTE:
    You hadn't explicitly defined NewRoom so it was being treated as a variant datatype
    If DeskNumber is an integer then that must also be true for NewDesk
    I have removed unnecessary [] and superfluous quotes "" that Access loves to insert
    I've also removed .Value - not needed as its the default

    The above code is based solely on your last post. I hadn't seen Arvil's response at the time of posting so haven't taken his comments into account
    Last edited by isladogs; 06-16-2018 at 10:50 AM.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  11. #11
    hinchi1 is offline Competent Performer
    Windows 10 Access 2013 32bit
    Join Date
    Sep 2017
    Posts
    181
    Thanks for your feedback, but i am still getting a datatype mismatch message. i have changed to your suggested code, but i am unsure of something. The RoomID is an integer but the desk number can be listed as "D3". So would this be a string? The desk number can also be an integer "1". I have copied the code i am struggling below and highlighted the line which is picking up on the datamismatch. it may just be the syntax is wrong. Thanks again for your help.


    ----------------------------------------
    Private Sub txtDeskNumber_AfterUpdate()

    'Dim NewRoom, NewDesk As String
    Dim NewDesk As Integer
    Dim NewRoom As String
    Dim stLinkCriteria As String
    Dim Desk As Integer


    'Assign the entered room number and desk number
    NewRoom = Me.cboRooms.Value
    NewDesk = Me.txtDeskNumber.Value
    'stLinkCriteria = "[RoomID] = " & "'" & NewRoom & "' and [DeskNumber] = " & "'" & NewDesk & "'"
    stLinkCriteria = "RoomID = " & NewRoom & " And DeskNumber = " & NewDesk
    'If Me.cboRooms = DLookup("[RoomID]", "qryDeskInformation", stLinkCriteria) Then
    If Me.cboRooms = DLookup("RoomID", "qryDeskInformation", stLinkCriteria) Then
    MsgBox "This room, " & NewRoom & ", has already been entered in database." _
    & vbCr & vbCr & "with DeskNumber " & NewDesk & "" _
    & vbCr & vbCr & "Please check customer name and address again.", vbInformation, "Duplicate information"
    Me.Undo 'undo the process and clear all fields
    'show the record of matched room number and desk number from the desk information table
    Desk = DLookup("[RoomID]", "qryDeskInformation", stLinkCriteria)
    Me.DataEntry = False
    DoCmd.FindRecord DeskNumber, , , , , acCurrent
    End If
    End Sub
    ----------------------------------------------------------------------------------------------------------------

  12. #12
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    The RoomID is an integer but the desk number can be listed as "D3". So would this be a string? The desk number can also be an integer "1".
    You are getting your datatypes & delimiters muddled up ... or I am!
    NewDesk needs to be a string as DeskNumber may contain text and therefore it needs text delimiters
    NewRoom needs to be an integer as RoomID is an integer

    Another reminder you do not need .Value

    If I understand your code correctly:
    a) the first part clears the data entry if already used
    b) the second part assigns the value if not currently in use
    Therefore I believe it should have an Else section

    Corrected code below. Please replace your entire code block with this and see if it does what you want
    NOTE: Items in GREEN are comments, items in RED are changes to code

    Code:
    Private Sub txtDeskNumber_AfterUpdate()    
    Dim NewRoom As Integer
    Dim NewDesk As String
    Dim stLinkCriteria As String
    'Dim DeskNumber As String - OMIT - assuming its a field name
    
    
    'Assign the entered room number and desk number
    NewRoom = Me.cboRooms 'this assumes the bound column of the combo is a number field
    NewDesk = Me.txtDeskNumber
    
    
    stLinkCriteria = "RoomID = " & NewRoom & " And DeskNumber = '" & NewDesk & "'"
    
    
    If Me.cboRooms = DLookup("[RoomID]", "tblDeskInformation", stLinkCriteria) Then
    
    MsgBox "This room, " & NewRoom & ", has already been entered in database." _
    & vbCr & vbCr & "with DeskNumber " & NewDesk & "" _
    & vbCr & vbCr & "Please check customer name and address again.", vbInformation, "Duplicate information"
    Me.Undo 'undo the process and clear all fields
    Else 'show the record of matched room number and desk number from the desk information table DeskNumber = DLookup("RoomID", "tblDeskInformation", stLinkCriteria) 'OMIT? - you already have this info from Me.txtDeskNumber/NewDesk Me.DataEntry = False DoCmd.FindRecord DeskNumber ', , , , , acCurrent '<== do you need acCurrent here? Try removing it End If End Sub
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  13. #13
    Join Date
    Apr 2017
    Posts
    1,673
    In Access, field can have only one datatype. So even when all other desk numbers are textnumeric, and only one has number like "3D", the field must be string - otherwise you can't enter this text number into table.

    In case you have separate Room and Desk tables too, then in those you can have autonumeric fields as primary keys, and e.g. desk number as additional field (it is reasonable, when you need some keep some additional information like room destination, person responsible for room, item/desk type, inventory munber of item/desk, etc.). Then you can in table, where you register item/desk locations, use long integer fields matching with autonumeric primary keys in other tables, instead of desk room and desk numbers

  14. #14
    hinchi1 is offline Competent Performer
    Windows 10 Access 2013 32bit
    Join Date
    Sep 2017
    Posts
    181
    Thanks for the updated code which works. However, it has thrown up other issues with clicking the Save command button, but i will figure this out. one thing though with the message box to indicate duplicate records. Instead of displaying the RoomID number, is there a way to show the room name in the message box? i want to grab what is showing in the rooms combobox and display the name. Thanks again for your help. So where it says "This room, 3". i would like to display the room name such as "AMB-01". Is this possible?
    Click image for larger version. 

Name:	RoomID.JPG 
Views:	12 
Size:	19.2 KB 
ID:	34464
    Attached Files Attached Files

  15. #15
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    You haven't said where the room name info is stored
    Assuming its the 2nd column in your combo (i.e. Column(1) as zero based) then add these lines to your code:

    Code:
    Dim strRoom as String
    strRoom =  Me.cboRooms.Column(1)
    
    and change your message box code to use strRoom instead of NewRoom

    Or you could use a DLookup.
    Something like this, but with your field & table names
    Code:
    strRoom = DLookup("RoomName","tblRooms","RoomID = " & Me.cboRooms)
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

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

Similar Threads

  1. Desk Count
    By hinchi1 in forum Access
    Replies: 2
    Last Post: 06-07-2018, 11:06 AM
  2. Replies: 2
    Last Post: 06-01-2016, 08:03 AM
  3. Desk top to mobil app
    By BEI77 in forum Access
    Replies: 2
    Last Post: 03-23-2016, 04:37 PM
  4. Replies: 4
    Last Post: 12-13-2015, 06:46 PM
  5. Replies: 4
    Last Post: 01-05-2014, 02:09 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