Access confusion...?

[saladbarkid]

Right now, I have txtData displaying all the names in a text box on the form. I need to be able to take the fields from strData and list them all in a database with the same column names. How do I do this?? Thanks in advance! Here's the code:


Option Compare Database
Option Explicit

Private Sub Command8_Click()
Dim strSql As String
Dim d As Database
Dim rs As Recordset
Dim strFrom, strTo, strData As String

strFrom = Me.DOBFROM & ""
strTo = Me.DOBTO & ""
strData = ""

If strFrom = "" Or strTo = "" Then
MsgBox "Need dates in both fields"
Exit Sub
End If

Set d = DBEngine.Workspaces(0).Databases(0)

strSql = "SELECT * FROM names WHERE DOB >= #" & strFrom & "# AND DOB <= #" & strTo & "#"

Set rs = d.OpenRecordset(strSql)

While rs.EOF <> True And rs.BOF <> True
strData = strData & rs![LASTNAME] & rs![FIRST] & rs![ADDRESS] & rs![CITY] & rs![STATE] & rs![ZIP] & ";"
rs.MoveNext
Wend

txtData = strData
End Sub

[AltatemTC]

Sorry saladbarkid, I don't get it. You have them in a database already because that is how you are filling txtData. What do you mean by

I need to be able to take the fields from strData and list them all in a database with the same column names.

????

[AltatemTC]

One thing you might consider to simplify your code.

While rs.EOF <> True And rs.BOF <> True

Use "Do Until rs.EOF". If there are no records EOF will be true and if you are past the last record EOF will be true.

Then replace your Wend with a Loop.

[saladbarkid]

What I meant was I have a textbox (txtData) that has the items that met the criteria displayed in it. What I need to do is take (strData = strData & rs![LASTNAME] & rs![FIRST] & rs![ADDRESS] & rs![CITY] & rs![STATE] & rs![ZIP] & ";") those items and insert them into a table with the same column names. Help!

[AltatemTC]

I am making the assumption that it can be done from the original table. You didn't explicitly state where the data is coming from "the table" or "the textbox". If you want to pull the data from the table use the following"

"SELECT LASTNAME, FIRST, ADDRESS, CITY, STATE, ZIP INTO TABLE2 FROM TABLE1 WHERE DOB>=#" & strFrom & "# And DOB<=" & strTo & "#;"

You may have to tweak the statement a little bit. I didn't test it but it should point you in the right direction.

Just a quick recommendation, anytime you discuss a programming problem, be sure you state the inputs, outputs and intention of the operation. That helps everyone understand exactly what you are doing and can expedite answers.

[saladbarkid]

Thanks alot! I'll give it a shot now.

[saladbarkid]

Well, that didn't exactly work. I keep getting errors. I'm pulling the data from a table called names and I want the sorted data to be inserted in to a table called selection. Any suggestions??

[AltatemTC]

What are the errors?