Showing select information

[lithium]

The main purpose is that another person can select an issue in the box, change the status from active to resolved then it will drop off the list. I can't post code right now cause I'm at work and I really haven't even used to much anayway

[lithium]

I'm going to change this now. I will add an Edit button to the main form, when the user highlights a record in the list box, he can click it and change the status to resolved. So basically I need to find out how to get the proper record that the have selected and change the value with a query

[ItsMe]

An action query to update the record may be appropriate. Something like

'Change the value of the YesNo field
'in tblMain to True where the PK value
'matches the form's current record
Dim strSQL As String
strSQL = "UPDATE tblMain " & _
         "SET tblMain.YesNo_Field = -1 " & _
         "WHERE tblMain.PK_Field =" & Me.List48.Column(0)

CurrentDb.Execute strSQL, dbFailOnError 'the fail on error is optional

[lithium]

Right. I haven't been able to even get the row selection to work though. I can't figure out how to know what row and pull all that information though. I tried what you mentioned with no luck

[lithium]

I just need to figure out how to know what row the user selected and fire off a query to change the value under Status column to "Resolved". It isn't set as yes or no rather but a combo box that had two options: either Active or Resolved

[ItsMe]

If your combo value is text then maybe you can update a string variable in the afterupdate event of the combo.

Dim strAnswer as string
strAnswer = Me.cmbComboName

Then your SET clause in your SQL string would be

"SET tblMain.YesNo_Field = '" & strAnswer & ''" & _

you could also
"SET tblMain.YesNo_Field = '" & sMe.cmbComboName & ''" & _

You will want to do some validation somewhere (Before running the SQL) to make sure your combo got updated and it has a value though

[lithium]

That doesn't solve anything for me though. I have no "Yes/No" in the database like I mentioned and I don't need to worry about that stuff yet as I can't even get a hold of the row selection...if I can't tell which row they are clicking then I can't update the proper record in the database.

That combo box has nothing to with it all and is on a separate page only being used when the user makes a new repair request. The only thingon my main page is a listbox full of all records in my table shown as "Active". Meaning I need to be able to simply let the user click on the proper row they want, click a command button that will now just say "Mark as Resolved" and the code for the button once clicked will figure out what record in the list is highlighted (was clicked on anyway) and then send a query to change the value in the Status column to being "Resolved".

[ItsMe]

Perhaps the code in post # 13 will help you determine how to get he appropriate information.

[June7]

What value in combobox RowSource can be used to uniquely identify desired record?

Show properties of combobox:

RowSource (sql statement)
BoundColumn
ColumnCount
ColumnWidths

[lithium]

Sorry if that came off as angry, been working at this for hours and feeling frustrated =/.

June7: I hope you meant listbox not combobox, this is the listbox properties:

RowSource: SELECT [qActive].[Title], [qActive].[Assigned to], [qActive].[Requested by], [qActive].[Status], [qActive].[Priority], [qActive].[Comments] FROM qActive ORDER BY [Priority];
Bound Column: 1
Column count: 6

I think I found a mishap on my part, I didn't extract the ID to be put in the listbox (I simply didn't want the user to see it though.

[June7]

Okay, listbox.

The ID column can be hidden by setting its width to 0. You did not provide ColumnWidths.

Make ID the first column with 0 width and still bind to column 1.

Use it in code to update desired record. Back to ItsMe code in post 18. There is a critical error. Variables need to be concatenated. Reference to control on form is a variable.

"WHERE ID = " & Me.listboxname

[lithium]

Hah, I think I made some headway.

I used this:

lblTest.Caption = Me.List48.Column(3)

Under the Double Click Event, this at least shows me the Active status for the record I'm highlighting, but how would I twist this to fire off a query to change that value to another "resolved"?

[ItsMe]

I corrected the code in Post 18. As June noted, I did not concatenate the Where criteria correctly. You could place that code in any event you wish. I would recommend that you let the user know they are updating a record though. For instance, you may want to use a msgbox like vbOKCancel or vbYesNo if you decide to include the update query in your double click event. Maybe you can bypass the msgbox and place the code in a click event of a separate control button. The user selects the record and then clicks a button to do the update.

When you mention, "Twist". Maybe you need to check the current status of the selected record and update the record to be the opposite of its current status. You can do a validation by retrieveing the value of column 3....

[lithium]

I've made some good headway but hit another dead end. Right now I'm running my Edit form (User double clicks the selection from a listbox on frmMain and this Edit form should pop up, filled with the information in which they selected in frmMain). But I can't seem to figure out the proper code to fire off a SQL statement that limits it down to being the selected ID number (filtered).

Right now I have this in my frmEdit On Load event -

Private Sub Form_Load()
Dim intID As Integer
Dim strFind As String

If Me.OpenArgs > 0 Then
intID = Me.OpenArgs
strFind = "SELECT * FROM Table1 WHERE ID = 1"
DoCmd.RunSQL strFind
Label6.Caption = strFind
End If

End Sub

Basically the intID is being passed through Open Args when they double click the listbox on frmMain (it grabbed the Column 0 and sent it that way).


::EDIT:: The reason I used WHERE ID = 1 is just a test-case to see if it would work or not, originally this value for ID should be set to the intID variable value.

[ItsMe]

What are you trying to accomplish in the load event handler?