none
HOW FIND FIELD IN TABLE USING LIST BOX AND ALLOW FIELD TO BE EDITABLE BY TEXT BOX RRS feed

  • Question

  • Hey guys,

    On my database I have a table with several fields on it. One of these fields is called "STATUSBOARDNOTE." This field I use to allow my end user to type down small notes that help remind them of the particular circumstance of the record before the status is changed from "PENDING" to something else. With that said... I have a Form with a listbox in it that has all "PENDING" records, once the record is changed from "PENDING" to something else, the record disappears from this list and goes into the main database. Well, I want to add a textbox to my form that is linked to "STATUSBOARDNOTE" in my table where records are kept - the issues I'm coming across is, when I select a record from the list, the textbox does not go to the record I selected, therefore when I type a note it's randomly stored! I've tried to use similar code to how I created my search forms, but I have been unsuccessful. Any assistance would be appreciated. Thank you.


    • Edited by InnVis Thursday, June 15, 2017 10:34 PM
    Thursday, June 15, 2017 7:08 PM

Answers

All replies

  • Hi,

    Is your listbox bound to the ID column for the table you're trying to update? If not, try binding it to the ID field and then use the ID field to UPDATE the table for the specific record selected on the listbox.

    Hope it helps...

    Thursday, June 15, 2017 8:12 PM
  • Yes, the listbox works great with the form it's in, the only item that is not updating is the unbound textbox I created within the form.
    • Edited by InnVis Thursday, June 15, 2017 8:25 PM
    Thursday, June 15, 2017 8:25 PM
  • Can you show us the code you're using? Thanks.
    Thursday, June 15, 2017 8:58 PM
  • Private Sub SearchResults_DblClick(Cancel As Integer)
    
        Dim stDocName As String
        Dim stLinkCriteria As String
    
        stDocName = "frmViewEditRecord"
        stLinkCriteria = "[BILLING NUMBER]=" & Me![SearchResults]
    
    If [SearchResults].[ListCount] = 0 Then
    Exit Sub
    ElseIf [SearchResults].[ListCount] > 0 Then
        DoCmd.OpenForm stDocName, , , stLinkCriteria
    End If
        
    End Sub

    The code is set on the double click event of the list box, it opens up a different form to allow me to edit the values of that record, but I don't really want the STATUSBOARDNOTE in that form, instead I want it to be a dynamic value in the text box.
    Thursday, June 15, 2017 9:03 PM
  • Hi,

    Thanks for the additional information. Maybe you could try the following modified version of your code:

    Private Sub SearchResults_DblClick(Cancel As Integer)
    
        Dim stDocName As String
        Dim stLinkCriteria As String
    
        stDocName = "frmViewEditRecord"
        stLinkCriteria = "[BILLING NUMBER]=" & Me![SearchResults]
    
    If [SearchResults].[ListCount] = 0 Then
    Exit Sub
    ElseIf [SearchResults].[ListCount] > 0 Then
        CurrentDb.Execute "UPDATE TableName SET StausBoardNote='" & Me.TextboxName & "' WHERE " & stLinkCriteria, dbFailOnError
        DoCmd.OpenForm stDocName, , , stLinkCriteria
    End If
        
    End Sub

    Hope it helps...

    Thursday, June 15, 2017 9:13 PM
  • I tried adding the STATUSBOARDNOTE to the qryPendingStatusBoard, but the textbox didn't pull the data from the record. I also tried messing around with code like Me.txtStatusBoardNote.Value = Me.SearchResults (txtStatusBoardNote is the textbox and SearchResults is the list box with records from the query). I also attempted to do = [SearchResults].[Column](9) and I placed the STATUSBOARDNOTE from my table into the query on the 9th column of the listbox, but it didn't allow me to edit the textbox.
    Thursday, June 15, 2017 9:16 PM
  • That code works, but I want it to update the open text box in the form rather than use the "DoCmd.Openform stDocName, , , stLinkCriteria" line
    Thursday, June 15, 2017 9:17 PM
  • Hi,

    Not sure I understand but you could try using DLookup(). For example:

    Me.TextboxName = DLookup("StatuBoardNote", "TableName", strLinkCriteria)

    Hope it helps...

    Thursday, June 15, 2017 9:35 PM
  • Tried the DLoopup option and it's not quite working. I can get it to show up, but not able to edit it. Essentially I need a text box field that allows me to write random notes in so I can get misc. information on that record. You see this database tracks the insertion of PICC lines (peripherally inserted central catheters) for a military hospital I work for. I made this form that acts as a statusboard showing all of the pending patients that need a PICC line. So, each time I create a new record, if they are set to "PENDING" status, then I can see it in the status board... once I insert the PICC line I double click, it opens up my data entry form and I enter specific data and change the status to either "SUCCESSFUL, UNSUCCESSFUL, or CANCELLED." When I change the status it drops it from the statusboard and stores it in another form that acts as a search engine for records. The situation I'm having is that sometimes I need certain things from my colleagues before I can give the green light for a PICC line insertions... i.E. consent form, certain lab results etc... So, there is no way for me to know if the patient are good to go for an insertion of if there is something else we are waiting for. So I created this new field in my table where the records are stored and that field is called "STATUSBOARDNOTE" ... my idea is while I'm in the statusboard and I select a record (not double click, just select), it allows me to write misc. notes in this unbound text box. I can actually get the box to pull the record, but I can't get the textbox to allow me to enter/edit that field.
    • Edited by InnVis Thursday, June 15, 2017 10:30 PM
    Thursday, June 15, 2017 10:28 PM
  • Is your form bound? If not, you might try using a bound form to see if it makes things easier.
    Thursday, June 15, 2017 11:10 PM
  • My form is bound to a query which pulls the list box.
    Friday, June 16, 2017 12:25 AM
  • Good. Did you say you tried to include the field in your query? Maybe this time, you could also try binding the textbox to the field. Or, at least, try adding the field on the form, so you can see its contents. We can then use code, if you still want, to update the field with the one from the textbox.
    Friday, June 16, 2017 12:36 AM
  • Alright, so last night after getting off work and before my wife kicked me off the computer I believe I got a step closer to the answer, but I'm still missing a small piece I'm hoping you guys might know. Yes, I have the STATUSBOARDNOTE field in my query, I can pull the value of the column through the list box. In my screenshot, if you seen the bottom left corner of the form the value of those text boxes are =[SearchResults].[column](0)...=[SearchResults].[column](1)... etc. If I add this value to the text box I can view the note from the selection in the list box (by the way [SearchResults] is the list box), but since it's an expression I can't modify the text of STATUSBOARDNOTE. What I did managed to do was, create a form directly attached to the table and I am able to pull the STATUSBOARDNOTE and modify it this way, but I want it to appear on my current form within the blank space below the [SearchResults] list box, that way my end user can view the note as I click through records within the list box instead of having to open a separate form.
    Friday, June 16, 2017 10:56 AM
  • Hi Alexander,

    I am not sure we are understanding each other. Is the field in the record source of the form or just in the row source of the listbox? Is the form bound to the same table/query for the listbox? To confirm, can you please post the SQL for the listbox and also for the form? Thank you.

    Friday, June 16, 2017 3:56 PM
  • Here it is, sorry I'm all self-taught coder total novice when it comes to the specifics of the language. I had to actually google how to find this SQL!

    this is the SQL of the form where the list box and the text box are located in:

    SELECT tblPatientDatabase.STATUSBOARDNOTE
    FROM tblPatientDatabase;
    

    this is the SQL of the list box located within the form above:

    SELECT tblPatientDatabase.[BILLING NUMBER] AS [BILLING #], tblPatientDatabase.[INSERTION DATE] AS [PROMISED DATE/TIME], tblPatientDatabase.[LAST NAME], tblPatientDatabase.[FIRST NAME], tblPatientDatabase.WARD, tblPatientDatabase.INSERTER, tblInsertionStatus.InsertionStatus AS STATUS, tblPatientDatabase.[INSERTION STATUS], tblPatientDatabase.STATUSBOARDNOTE
    FROM tblInsertionStatus INNER JOIN tblPatientDatabase ON tblInsertionStatus.ID = tblPatientDatabase.[INSERTION STATUS]
    WHERE (((tblPatientDatabase.[INSERTION STATUS])=1))
    ORDER BY tblPatientDatabase.[INSERTION DATE];
    

    Friday, June 16, 2017 4:24 PM
  • Hi,

    Thanks. What is the primary key for tblPatientDatabase table? Try changing the SQL for the form to this:

    SELECT * FROM tblPatientDatabase

    Then, in design view of the form, drag the STATUSBOARDNOTE from the Field List to the Details section of the form.

    When you select an item from the Listbox, do you have any AfterUpdate event code running? If so, can you please post it here?

    Thanks.

    Friday, June 16, 2017 4:29 PM
  • The primary key for tblPatientDatabase is an autonumber running a replication ID. It won't let me drag the STATUSBOARDNOTE to the Details section of the form for some reason and the code for the Listbox AfterUpdate is:

    Private Sub SearchResults_AfterUpdate()
        
        UpdateSearch Me.txtStatusNote, Me.SearchResults
        
    End Sub

    I get an error on the code above stating that sub was not defined.

    • Edited by InnVis Friday, June 16, 2017 5:28 PM
    Friday, June 16, 2017 5:21 PM
  • Hi Alexander,

    Looks like this is getting more complicated trying to troubleshoot long-distance. Are you able to post a copy of your database? It might speed things up a bit.

    Just a thought...

    Friday, June 16, 2017 5:33 PM
  • I think I have what you are trying to do set up in a form that I am using right now, which I am pretty sure is working properly at this point because of .theDBguy's help in my question thread!

    ---

    I set the RowSource of the List Box Control to an SQL Query using the Property Sheet in the Design View. The List Box is named PCSPicker, which you will see in the following snippets.

    That last line of this query is where the magic happens, where the query can pull the value for the filter from an Unbound Control in my form, CMHCNumber.

    SELECT ID, PeerAssigned, MeetingDate, ReasonForMeeting
    FROM PeerCaseSummaries
    WHERE CMHCNumber = [Forms]![PeerCaseSummary].[CMHCNumber].[Value];
    This query populates the list box based on the value in CMHCNumber. In order to make that happen, the AfterUpdate event for the CMHCNumber Control calls Me.PCSPicker.Requery

    Next, you need to link a function like this to the event of clicking on the List Box (or double clicking depending on how you want your UI to work). ID is an automatically numbered primary key for the PeerCaseSummaries table, so this filter will always only return a single record.
    Private Sub PCSPicker_Click()
        Me.RecordSource = "PeerCaseSummaries"
        Me.Filter = "[ID] = " & Me.PCSPicker.Value
        Me.FilterOn = True
    End Sub

    The rest of the controls in the form are Bound to the various fields in the PeerCaseSummaries table.




    Friday, June 16, 2017 5:47 PM
  • Sure! I appreciate you looking into this. Here's a OneDrive link https://1drv.ms/u/s!AtTN2rh-oaUfjDhwLfOu4Z1T0uUT




    Hi Alexander,

    For some reason, the link didn't work for me. OneDrive said the file may have been deleted or expired.

    You can also send it to me via email.

    Cheers!

    Friday, June 16, 2017 5:58 PM
  • Alright, I'm emailing it to you at theDBguy@gmail.com it's coming from ABatista.RN@outlook.com
    Friday, June 16, 2017 6:04 PM
  • Got it. What is the name of the form?
    Friday, June 16, 2017 6:13 PM
  • Is it "frmSearchRecords?" Do you want to "add" a textbox on this form to edit the status? Or, did you want to be able to edit the status already displayed? Also, how would you like to trigger the update to the table (since this form is unbound)? For example, would you like a button next to the Status textbox to "save" the changes to the table?
    Friday, June 16, 2017 6:20 PM
  • the form I need to add the textbox to is frmStartShell because that form acts as my "statusboard." Is it possible to make the textbox populate the data that already exists in that field associated to the record. I need to be able to edit that textbox. Would it be possible to have the textbox update as I select records from the list box and save itself as I type instead of having a button?
    • Edited by InnVis Friday, June 16, 2017 6:25 PM
    Friday, June 16, 2017 6:24 PM
  • Okay, I sent it back to your email.

    Hope it helps...

    • Marked as answer by InnVis Friday, June 16, 2017 6:52 PM
    Friday, June 16, 2017 6:47 PM
  • Wow, thanks! This is great, I was thinking of doing what you did also creating a subform, but I wasn't getting mine to update real time like what you did. I appreciate your help greatly. I'll see if I can reverse engineer it and learn from it.
    Friday, June 16, 2017 6:53 PM
  • Hi,

    You're welcome. Glad to hear it worked for you. Just let us know if you have any questions.

    Good luck with your project and thank you for your service!

    Cheers!

    Friday, June 16, 2017 6:56 PM
  • No. Thank you, for your trust.
    Friday, June 16, 2017 7:52 PM