5 Replies - 1690 Views - Last Post: 10 March 2017 - 12:03 AM Rate Topic: -----

#1 Rebel Spirit86   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 13
  • Joined: 14-December 16

How to populate text box values on combobox in VB access database

Posted 26 January 2017 - 08:07 AM

Hi friends,

I want to know about how to populate text box values on combo box in vb access database.

or how to get database values on combo box at form load event.

database values are stored in another table by form 2 and the combo box is placed on 1st form which enters the whole data into database.

also i am trying to make a popup for date reminder on check box button.

Thanks in advance

What I have tried:

here is my code:

Dim con As New ADODB.Connection
Dim rs As New ADODB.Recordset

Private Sub cmdExit_Click()
End
End Sub

Private Sub CmdSave_Click()
Set rs = New ADODB.Recordset
rs.AddNew
rs.Fields(CompanyName).Value = TxtCompanyName.Text
Clear
End Sub

Private Sub UserForm_Click()
con.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Users\mahtab.ali\Documents\NoticeBook.mdb;Persist Security Info=False"
rs.Open "Select * from CompanyName", con, adOpenDynamic, adLockPessimistic
Me.fillcombo
End Sub

Sub Clear()
ComboBox1.Value = ""
TxtCompanyName.Text = ""
End Sub

Sub fillcombo()
Do Until rs.EOF
ComboBox1.AddItem rs!CompanyName
rs.MoveNext
Wend
End If
End Sub


This post has been edited by modi123_1: 26 January 2017 - 08:10 AM
Reason for edit:: fixed botched code tags


Is This A Good Question/Topic? 0
  • +

Replies To: How to populate text box values on combobox in VB access database

#2 The Guardian   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 20
  • Joined: 18-November 15

Re: How to populate text box values on combobox in VB access database

Posted 08 March 2017 - 03:17 AM

Are you trying to just populate the combobox with the textbox or do you want it to store in the DB also?
Was This Post Helpful? 0
  • +
  • -

#3 Rebel Spirit86   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 13
  • Joined: 14-December 16

Re: How to populate text box values on combobox in VB access database

Posted 08 March 2017 - 01:09 PM

View PostThe Guardian, on 08 March 2017 - 03:17 AM, said:

Are you trying to just populate the combobox with the textbox or do you want it to store in the DB also?



Thanks for reply.

well my criteria is i am adding a company name from company details form in text field and relating the combobox on notice details form from the database table.

but it is not refreshing the combobox and showing the exact what i want

i wish u could help me out
thanks
Was This Post Helpful? 0
  • +
  • -

#4 The Guardian   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 20
  • Joined: 18-November 15

Re: How to populate text box values on combobox in VB access database

Posted 08 March 2017 - 01:47 PM

All you have to do is write your insert query to write the company name from the company details form into the DB.
Then on the Form_Load() event write a query to populate the combobox every time the form loads. This will give you an updated list on the combobox.

I use SQL as my back end so I'm not sure how to connect with Access but the general idea is the same.

This is how I would do this.

Dim SqlCon as new ADODB.Connection 'New Connection
Dim RecSet as new ADODB.RecordSet  'New RecordSet
Dim ConStr as string               'Connection String
Dim SqlQue as string               'SQL Query

Private Sub btnSave_click()
'This will Connect the DB
ConStr = "Provider=SQLOLEDB;Server={Server};Database={Database};User ID={User};Password={Password};

'Check to make sure connection isn't already open
'This will also prevent error
If SqlCon.State = 1 then SqlCon.Close
SqlCon.Open ConStr

'Pass the query into the string
'I break my query like this to make my code neater
'Single Quotes = Chr(39)
'Double Quotes = Chr(34)
SqlQue = "Insert Into {Company_Table}("
SqlQue = SqlQue & "Company_Name,Tel_No,etc,etc,etc)Values("
SqlQue = SqlQue & chr(39) & textbox1.text & chr(39)
SqlQue = SqlQue & "," & Chr(39) & textbox2.text & Chr(39)
'Continue the string until your query is completed...
SqlQue = SqlQue & ")"

'Open the recordset
RecSet.Open SqlQue,Sqlcon

'This will insert the data into the Database

End Sub




Now you need to retrieve the data from the DB and populate the combobox. This is how I would do that.

'This is the same as above but the declaration needs to be done again for this form also
Dim SqlCon as new ADODB.Connection 'New Connection
Dim RecSet as new ADODB.RecordSet  'New RecordSet
Dim ConStr as string               'Connection String
Dim SqlQue as string               'SQL Query

Private Sub Form_Load()
ConStr = "Provider=SQLOLEDB;Server={Server};Database={Database};User ID={User};Password={Password};

If SqlCon.State = 1 then SqlCon.Close
SqlCon.Open ConStr

SqlQue = "Select * from {Company_Table}"
RecSet.Open SqlQue,SqlCon

'Now we populate the table with the info

'We loop through the records to add the items one at a time
Do While not RecSet.EOF
ComboBox1.AddItem Recset("Company_Name")
RecSet.MoveNext
loop

RecSet.Close

SqlCon.Close

End Sub




That should do the trick.

Every time you open the form, it will repopulate the combobox. This will ensure that you always have the updated info.

If the form with the combobox is already open and you capture a new company's information, this will not be updated.

For that you will need to close the form and reopen it.

Hope that helps.
Was This Post Helpful? 0
  • +
  • -

#5 Rebel Spirit86   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 13
  • Joined: 14-December 16

Re: How to populate text box values on combobox in VB access database

Posted 09 March 2017 - 07:27 AM

Thank u Dear The Guardian.

For the help u provide me a very well solution.

I will try it for my criteria.

Many Thanks.. :tup:
Was This Post Helpful? 0
  • +
  • -

#6 The Guardian   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 20
  • Joined: 18-November 15

Re: How to populate text box values on combobox in VB access database

Posted 10 March 2017 - 12:03 AM

Please post the results back here and also don't forget to rate my solutions. :balloon:
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1