Results 1 to 9 of 9

Updating field in table with the value shown in text box in a form

  1. #1
    mp3909 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    122

    Updating field in table with the value shown in text box in a form

    I have a form that only consists of one Text Box which I have called txtCOBDate.
    This is what my form looks like - it sets the date in the text box to previous date.



    Click image for larger version. 

Name:	Capture1.PNG 
Views:	11 
Size:	5.4 KB 
ID:	39720

    I managed to set the date in the form to previous date using this code:

    Code:
    Private Sub Form_Load()
    
    
    Dim dt As Date
    Select Case Weekday(Now())
        Case 2:
            dt = Date - 3
        Case Else:
            dt = Date - 1
    End Select
    Me.Controls("txtCOBDate") = dt

    What I am trying to also do is to update a one and only field called COBDate in my table called tbl_COBDate
    I am also trying to do this within the same code as above using this line of code:

    Code:
    CurrentDb.Execute "UPDATE tbl_COBDate SET COBDate = #" & Format(dt, "mm/dd/yyyy") & "#"
    The code runs fine without any errors but its not updating the field in my table.

    Would anyone know why?

    I am trying to create a link between the the value in the text box in my form with the value in the field of my table - maybe there is another way to achieve this??

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    7,625
    you dont need any code,
    cant you just bind the text box to the field? Then whatever is in the box is in the field.

  3. #3
    mp3909 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    122
    ranman256, please can you not reply to my threads because you simply just "kill" it with one worded answers that is not helpful at all!!!
    And you demotivate others from answering cos you have already answered.
    Stop it, you don't help, in fact you annoy me.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    47,906
    Could just bind form to table and textbox to field.
    Code:
    Select Case Weekday(Now())
        Case 2:
            Me!COBDate = Date - 3
        Case Else:
            Me!COBDate = Date - 1
    End Select
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    mp3909 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    122
    ok, but what are the steps to bind form to table and textbox to field.
    Please can someone show me, i don't know and I searched on the internet for it.
    thank you.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    47,906
    That is very basic Access functionality. Have you studied an introductory tutorial book? Should be practically first chapter. How can you be knowledgeable about programming in VBA without knowing how to design bound forms in Access? That is a major appeal of Access - ease of building bound forms for data entry interface.

    Set form RecordSource to table and textbox ControlSource to field.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    mp3909 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    122
    that still does not explain why this piece of code does not perform:

    CurrentDb.Execute "UPDATE tbl_COBDate SET COBDate = #" & Format(dt, "mm/dd/yyyy") & "#"

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    47,906
    It works for me. Don't know your db so cannot explain failure.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    mp3909 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    122
    bizarrely, it now seems to work all of a sudden.....very strange indeed.

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

Similar Threads

  1. Replies: 11
    Last Post: 03-09-2018, 10:18 AM
  2. Text Box In Form Not Updating Table Field
    By dgarber in forum Forms
    Replies: 8
    Last Post: 11-22-2017, 03:15 AM
  3. Replies: 8
    Last Post: 02-07-2017, 09:58 AM
  4. Replies: 7
    Last Post: 10-25-2014, 10:41 AM
  5. Updating Table field from Form
    By Kunuk in forum Access
    Replies: 0
    Last Post: 02-26-2009, 11:41 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
  •  
Tech Forums: Microsoft Office Forums