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

    I have changed the code to the following, but get a missing operator, syntax message. Sorry to have to keep coming back to you.
    ------------------------------------------
    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
    Dim DeskNumber As String
    Dim strRoom As String


    '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
    strRoom = Me.cboRooms.Column(1)




    stLinkCriteria = "RoomID = " & strRoom & " And DeskNumber = '" & NewDesk & "'"




    If strRoom = DLookup("[RoomID]", "tblDeskInformation", stLinkCriteria) Then
    MsgBox "Room " & strRoom & ", has already been entered in to the database." _
    & vbCr & vbCr & "with desk number " & NewDesk & "" _
    & vbCr & vbCr & "Please check desk number again.", vbInformation, "Duplicate information"
    'Me.Undo 'undo the process and clear all fields

    txtDeskNumber.SetFocus



    'Else
    'show the record of matched room number and desk number from the desk information table
    'DeskNumber = DLookup("DeskID", "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


    'DoCmd.FindRecord DeskNumber, , , , , acCurrent

    End If
    End Sub
    ------------------------------------------------------------------------------------------------------------------------------------------

  2. #17
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    NO!!!!!
    Just add/change the lines I said - nothing else!

    Also why have you commented out this line as I suggested
    'Dim DeskNumber As String - OMIT - assuming its a field name
    then defined it again in the following line
    Dim DeskNumber As String

    Looking at your last post again, you've commented out everything including all of the Else section
    I'll try once more removing my comments & hopefully including all the code needed:

    Code:
    Private Sub txtDeskNumber_AfterUpdate()    
    Dim NewRoom As Integer
    Dim NewDesk As String
    Dim stLinkCriteria As String
    Dim strRoom As String
    
    'Assign the entered room number and desk number
    NewRoom = Me.cboRooms 
    NewDesk = Me.txtDeskNumber
    strRoom = Me.cboRooms.Column(1)
    
    stLinkCriteria = "RoomID = " & NewRoom & " And DeskNumber = '" & NewDesk & "'"
    
    If Me.cboRooms = DLookup("[RoomID]", "tblDeskInformation", stLinkCriteria) Then
    MsgBox "This room, " & strRoom & ", 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
         Me.DataEntry = False
         DoCmd.FindRecord DeskNumber = '" & NewDesk & "'"
    End If 
    
    End Sub
    I'm still not absolutely sure about the FindRecord line above but hopefully it will now work.
    Do try to understand this code rather than just copy code without thinking what it means
    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

  3. #18
    hinchi1 is offline Competent Performer
    Windows 10 Access 2013 32bit
    Join Date
    Sep 2017
    Posts
    181
    Quote Originally Posted by ridders52 View Post
    NO!!!!!
    Just add/change the lines I said - nothing else!

    Also why have you commented out this line as I suggested
    'Dim DeskNumber As String - OMIT - assuming its a field name
    then defined it again in the following line
    Dim DeskNumber As String

    Looking at your last post again, you've commented out everything including all of the Else section
    I'll try once more removing my comments & hopefully including all the code needed:

    Code:
    Private Sub txtDeskNumber_AfterUpdate()    
    Dim NewRoom As Integer
    Dim NewDesk As String
    Dim stLinkCriteria As String
    Dim strRoom As String
    
    'Assign the entered room number and desk number
    NewRoom = Me.cboRooms 
    NewDesk = Me.txtDeskNumber
    strRoom = Me.cboRooms.Column(1)
    
    stLinkCriteria = "RoomID = " & NewRoom & " And DeskNumber = '" & NewDesk & "'"
    
    If Me.cboRooms = DLookup("[RoomID]", "tblDeskInformation", stLinkCriteria) Then
    MsgBox "This room, " & strRoom & ", 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
         Me.DataEntry = False
         DoCmd.FindRecord DeskNumber = '" & NewDesk & "'"
    End If 
    
    End Sub
    I'm still not absolutely sure about the FindRecord line above but hopefully it will now work.
    Do try to understand this code rather than just copy code without thinking what it means
    Thanks again for your help. Just missed certain lines but works great now. Thanks again.

  4. #19
    hinchi1 is offline Competent Performer
    Windows 10 Access 2013 32bit
    Join Date
    Sep 2017
    Posts
    181
    thanks for this, all good now.

Page 2 of 2 FirstFirst 12
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