CODEWITHSUNDEEP
vbams-access
Mitrovic Marko
Mitrovic Marko

Reputation: 13

Adding new record to a subform in VBA

I found a couple of answers here, but nothing seams to fit.

Problem: A database table with two columns ID and Name; I have a textbox (let's call it TextBox1) and a subform (SubForm1) on a form and I want to write ID into the TextBox (or use barcode reader), query the table On Change and add found record to subform. I have a query and it works, what I can't do is keeping previously found record from being overwritten. I searched for solution and none seams to fit. I am new to VBA and any help will be much appreciated. Thanks.

What I have so far:

Dim SQL As String

Dim rs As Recordset

SQL = "SELECT *  FROM MyTable WHERE MyTable.ID =" & Chr$(34) & Me.TextBoxID.Text & Chr$(34)

Set rs = CurrentDb.OpenRecordset(SQL)

And query wokrs, but I don't know how to do something like

Me.SubForm1.AddRecord or Me.SubForm1.AddItem so that previous record is still there and new one is added?

Can it be done? If it can't be done with subform, can it be done with listbox?

Upvotes: 0

Views: 163

Answers (1)

Mitrovic Marko
Mitrovic Marko

Reputation: 13

I did it with the listbox, but it's ugly, If some one have solution for subform, please post it. Code:

Private Sub TexBox1_Change()

  Dim SQL As String

  Dim rs As Recordset

  SQL = "SELECT *  FROM MyTable WHERE MyTable.ID =" & Chr$(34) & Me.TextBox1.Text & Chr$(34)

    Set rs = CurrentDb.OpenRecordset(SQL)

 Me.ListBox1.AddItem(rs.fields(0) & ";" & rs.fields(1))

End Sub

' Headers for listbox are set in Form_Load, first item added is header, there is no way to set it from recordset if listbox Row Source type is set to 'Value', and it has to be so items can be added...

Upvotes: 1

Related Questions