• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to display record from the listbox in vba

Nipendra.dhiman

Nipendra.dhiman

Member
Hi all,


i have a question here,

i have two textbox the first is name and the second one is for id

here i am using both for save record in access database and it is working successfully but i need to show the name and id in textbox as well when i click on the saved record

let say example i have saved Name-"Nipendra" and ID-101 then i click on nipendra in list box then it should be show in name textbox as well


Regards

Nipendra
 
Hi nipedra!


It will be very helpful.. if you provide layout of your user form or xlsm file..
 
I have a listbox with two column name and id question is how to display the values in the text box when I click on the record in listbox


Nipendra
 
Hi,


Please refer the below link for uploading the workbook.


http://chandoo.org/forums/topic/posting-a-sample-workbook


Thanks,

SK
 
Hi SP,


my question is that how do I refresh the data after click on save button or whatever it is ,mean to say that when I click on the save or delete button the record is going to save or delete but it is not displaying after the click , when I reload the form then it is showing with the update record please do the need full for it.


feel free to ask any clarifications...


Regards

Nipendra
 
@Nipendra


Hi


as per my understanding you are using the UserForms and in that forms you have some text box mentioned. when you enter the data in text box and press the Command button for Save or whatever it may be the data is stored in the worksheet but the UserForm is not clear is it correct. If it is correct the please add the below code in the UserForm CommandButton


TextBox1.value=""

TextBox2.value = ""


End Sub


i think it will solve your problem if not then please refer us again


Thanks


SP
 
SP


Hi Again,


sorry for confusion here but i have attached the file please click on the "main form" button on sheet1 then it will show the main form but due to connection path it wouldn't be open there so please go through with code and my problem is that i am using access ado connection to save our record it is working fine i have saved some record and delete as well but not able to refresh it , i think this refresh word is creating confusion here let me explain to you-


i am clicking on the save button data will be save but it is not adding in to the listbox on the same time but saving in the database when i reload the form then it is showing with update data so do you have any idea how to do it here


PATH-

http://www.fileconvoy.com/dfl.php?id=gb616661be945aa56999263384ca22e73dec16d8e0


feel free to ask any confusion


Regards

Nipendra
 
@Nipendra


Hi


sorry to inform you that when you are attach some file in the above mention link some it comes correct file and some time it's crash. now i can't download your file due it's display as a system file


so please upload the file with drop box which is better to download


Thanks


SP
 
Hi, Nipendra.dhiman!


The file you uploaded is "Sample.rar" which stands for a compressed file with WinRar, but if you open it you'll find that it's internal structure is of an .xlsm file, so people who download it should rename it to "Sample.xlsm" in order to open it with Excel.


Regards!
 
Hi, Nipendra.dhiman!


I downloaded your sample.rar file, renamed it as sample.xlsm, opened it with Excel, clicked on button MainForm of Sheet3, got the form displayed but nothing more as there's no connection with the data source, as you correctly advised.


Then I went to the VBA editor, checked the forms, decided to view the 1st one (Emaster) as they're all very similar, and I got a little confused because of the names you used for the controls. That's to say, you left the default names that Excel gives to new controls, CommandButton1, CommandButton2, ... TextBox1, TextBox2, ... and that turns difficult to follow a code and identify an issue, even more if it can be tested because of the unavailable connection.


I strongly recommend you to give your controls significant names, as well as for the main variables you use within its code events.


As a blind shot, if the problem is that the value entered in TextBox3 (Employee name) when you click on CommandButton1 (Save) is added or updated or deleted correctly in the Access database but not displayed in ListBox1 (the big list in the form), you may try something like what you used in UserForm1:

ListBox1.AddItem "Nipendra"


Regards!
 
I am so obliged for you time and comments and you almost got my point, I request you to please ignore the textbox name or whatever it is ..........leave by the default name.

yes I knew it I can add the value by the click event with this code = ListBox1.AddItem "Nipendra"

but I am saving my data in access database which is saving perfectly but It is showing only in access table not in the listbox on the user form after the click, and when I reload the form then it is showing the saved value the same process is happening with delete command


i have attached the database file also for more clarification.


http://www.fileconvoy.com/dfl.php?id=g53575d88c0be0561999266514f32c7517c6c72dd2


Regards

Nipendra
 
Hi, Dhiman!


Thanks for uploading the Access DB, it helped a lot in debugging the code and get data displayed.


Considering the employees form and access table (Emaster) and checking within the Excel code for where ListBox1 gets filled I found that it's in the UserForm_Activate event, which it's only triggered when you show it.


So you have at least three choices:


a) The simplest but inefficient and somewhat coarse that can be slow with large data volumes: call the code in activate procedure each time you add, update or delete a record


b) One a bit more complex but less clumsy than previous: as you add, update or delete records from Access DB table, do the same with Excel userform listbox control


c) The more difficult but more effective too: link your Access table to your Excel listbox. Some links:

http://www.mrexcel.com/forum/excel-questions/606006-filling-listbox-recordset-access.html

http://support.microsoft.com/kb/149254


Regards!
 
I am sorry Sir I am not getting you and you almost got my point I need to only---

when I will click on the save button it should be save in to our database table (which is working fine) as well as in to the listbox1(which is not adding right now) actually I never work with vba before first time I am trying to do.


would appreciable if more visibility.


Regards

Nipendra
 
Hi, Dhiman!


As the last line of code in each Save, Update and Delete procedures include this line:

Listbox1FromRecordset


And add this code at the end of the module:

-----

[pre]
Code: 
Sub ListBox1FromRecordset()
rs.Open "SELECT [Eid], [Ename] FROM emaster ;", _
cn, adOpenStatic
rs.MoveFirst
Call listful
End Sub
[/pre]
-----


Regards!
 
You must log in or register to reply here.
Back
Top