?
Solved

ListBox MS Access 2005

Posted on 2013-01-30
9
Medium Priority
?
317 Views
Last Modified: 2013-02-04
Not sure if this is do-able.

I have a listbox on my form that is populating correctly
I have a click event on the ListBox and am getting the value of column  (1)


Private Sub lbvHoursSID_Click()
    varHours = Me.lbvHoursSID.Column(1)
    Me.lblMessage.Caption = varHours
    blah...blah...blah...

Is there any way to set the value of column(5) on a different textbox update event?

Something like...
    Me.lbvHoursSID.Column(5) = Me.txtHours
0
Comment
Question by:lrbrister
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38835244
first you have to set in the list box property
Column Count 5  ' the least number of columns you can have could be more

now you can use this
Me.txtHours = Me.lbvHoursSID.Column(5)


you can also specify the Column Widths
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38835247
Not directly like that.

To change a column value, you'd have to change the rowsource query, or change the value in the table that the rowsource is drawn from.

What is your overall goal for this idea?  Can you use a separate control to show this value instead of changing the column?
0
 

Author Comment

by:lrbrister
ID: 38835278
Here's my goal.
I have a listbox that loads the type of hours based on customer.

I can't have a jillion hours data entry boxes all over the form.

So...I want to click on the hours type description (unique per customer) and then enter the hours in a single text box.
When I tab off of that textbox, I want to store the value BACK in the hours column in the listBox.

You can see my controls below...
I'ts looking like I may have to feed these various values BACK into my recordset?
The other columns aren't visible.  The column I'm trying to set is the (5)

This is the SUB that sets the record set
Private Sub loadSID()
    Dim cmd As New ADODB.Command
    cmd.ActiveConnection = Conn
    cmd.CommandType = adCmdStoredProc
    cmd.CommandText = "usp_HoursTrackingSIDGet"
    Set Me.lbvHoursSID.Recordset = cmd.Execute()
End Sub

Open in new window


This is my screen print
screenprint
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 61

Expert Comment

by:mbizup
ID: 38835310
Okay...

Its not clear how you are storing the data for the listbox, but I think the code behind the textbox (Exit Event perhaps) would be something along these lines:

Dim strSQL as string

' Update your table
strSQL = "UPDATE YourListBoxTable SET Hours = " & me.txtHoursReg & " WHERE Definition = 'Hours - Regular' AND PersonID = " & Me.PersonID
CurrentDB.execute strSQL, dbFailOnError + dbSeeChanges

'Re-load the listbox
LoadSID

Open in new window

0
 

Author Comment

by:lrbrister
ID: 38835337
mbizup
This has to stay client side.
There may be dozens of people updating hundreds of  records for the same customer at the same time.

On the client side is there no way of making provate recordset variable and updating the record for a particular row and then rebinding the listbox to THAT recordset?

In .Net this isn't an issue...I'd just cache everything and feed it back into the dataset/datatable.

But these folks are still in MS Access and I have to deal with this.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38835392
Okay try this from the exit or lost focus event of  your text box:

dim rs as DAO.recordset
set rs = Me.lbvHoursSID.Recordset
rs.FindFirst "Definition = 'Hours - Regular'"
rs.Edit
rs!Hours = me.txtHoursReg
rs.update
Me.lbvHoursSID.Requery

Open in new window

0
 

Author Comment

by:lrbrister
ID: 38835639
mbizup
That didn;t work but after moving things around...I'm looping through (to see if I can)

The code is failing on the rst.Fields("Hours").Value = 30 line.

Screenprint of error message follows

Screenprint
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 38835737
lrbrister,
<This has to stay client side.>


if i am reading it correctly, you don't need to update the "TABLE" used as the rowsource of your listbox?

but, you need to see changes in the RowSource of your listbox,  is that correct?


if that is the case, you may need to use disconnected recordsets

see this links

http://support.microsoft.com/?kbid=281998

http://support.microsoft.com/kb/195082
0
 

Author Closing Comment

by:lrbrister
ID: 38851020
Capricorn1,
 Sorry for the late get back.

Thanks
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question