Results 1 to 8 of 8
  1. #1
    Rizvi is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2017
    Posts
    12

    creating an edit form

    hello everyone


    if any one can help me out i am trying to create an edit form which will take value from user which is "Txn_number" (auto number field) and search in the table and extract the record for editing when user clicks on button, i am using the following code. the problem is when user clicks the button another popup input box is appearing. i think there's some problem in SELECT statement as i am searching for a field which is not text. if any one can help me out.

    Thanks in advance

    Dim strsearch As String
    Dim Task As String
    'Check if a keyword entered or not
    If IsNull(Me.txtsearch) Or Me.txtsearch = "" Then
    MsgBox "Please type in your search keyword.", vbOKOnly, "Keyword Needed"
    Me.txtsearch.BackColor = vbYellow
    Me.txtsearch.SetFocus
    Else
    strsearch = Me.txtsearch.Value
    Task = "SELECT * FROM tbl_fxmain WHERE (Txn_Number) = strsearch"
    Me.RecordSource = Task
    Me.txtsearch.BackColor = vbWhite
    End If

  2. #2
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You have to concatenate the variable into the string. Since it appears to be text and you want a wildcard search, something like:

    Task = "SELECT * FROM tbl_fxmain WHERE (Txn_Number) Like '*" & strsearch & "*'"
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Create an unbound combobox which shows all the possible values of Txn_number. Have the table as the record source of the form. Then in the AfterUpdate event of the combobox, add this code:
    Me.Filter="Txn_number=" & Me!comboboxname
    Me.FilterOn=True

    Note: tis assumes that Txn_number is a numeric field, and also assumes that it is the first column in the row source SQL for the combobox. If either of these are not so, then things will be different.

  4. #4
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    I think youre using too much code.
    In a continuous form showing all records, put a textbox in the header.
    user enters the value ,press enter.
    the code below shows.
    Code:
    sub txtSearch_Afterupdate()
    If IsNull( txtSearch) Then
       Me.FilterOn = False
    Else
       Me.Filter = "[ID]=" & me. txtSearch 
       Me.FilterOn = False
    End If
    end sub
    User selects the record, then clicks the EDIT button, and it opens in a form:

    docmd.openform "frmEdit",,,"[id]=" & txtID

  5. #5
    Rizvi is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2017
    Posts
    12
    Quote Originally Posted by pbaldy View Post
    You have to concatenate the variable into the string. Since it appears to be text and you want a wildcard search, something like:

    Task = "SELECT * FROM tbl_fxmain WHERE (Txn_Number) Like '*" & strsearch & "*'"

    thank you very much the above code worked.

  6. #6
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Happy to help.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    Rizvi is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2017
    Posts
    12
    hello everyone can you please advise where to put a check for no record found in the following code

    Private Sub btnsearch_Click()
    Dim strsearch As String
    Dim Task As String
    'Check if a keyword entered or not
    If IsNull(Me.txtsearch) Or Me.txtsearch = "" Then
    MsgBox "Please type in your search keyword.", vbOKOnly, "Keyword Needed"
    Me.txtsearch.BackColor = vbYellow
    Me.txtsearch.SetFocus
    Else
    strsearch = Me.txtsearch.Value
    Task = "SELECT * FROM tbl_fxmain WHERE (Txn_Number) Like '*" & strsearch & "*'"
    Me.RecordSource = Task
    Me.txtsearch.BackColor = vbWhite
    End If
    End Sub

  8. #8
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    One way would be a DCount() with the same criteria, right before setting the record source.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Replies: 18
    Last Post: 08-02-2017, 12:56 PM
  2. Replies: 21
    Last Post: 08-05-2013, 06:23 AM
  3. Replies: 1
    Last Post: 05-31-2013, 08:53 AM
  4. Replies: 1
    Last Post: 12-04-2011, 09:11 PM
  5. Creating "Edit Record" link in table column
    By joshearl in forum Forms
    Replies: 1
    Last Post: 12-25-2009, 11:17 AM

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