Well, you know what fields are being returned from your query. Create a temp table in an Access database that would store each piece of this information.
I know how to insert values into a table using;
INSERT INTO tablename (column1, column2,....)
VALUES (value1, value2,....)
But I have no idea how to insert a recordset.
Why not just use the PRINTER object to print the FLEX GRID data?
You know the TWIPS size of each flex grid column and can even use the .TEXTWIDTH property to measure the actual text...
All this allows for some easy printing with the PRINTER object - using the .CURRENTX setting to move from column to column on the paper.
We have a general routine to print all the flex grids on a form to paper. First it sizes up the data in the flex grids - determines if 8 or 10 fontsize is going to be used - determines if portrait or landscape is going to be used. Even asks if legal paper tray should be used if the data is that big...
Works nice for us - wasn't all that hard to develop.
*** Read the sticky in the DB forum about how to get your question answered quickly!! ***
Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".
I know how to insert values into a table using;
INSERT INTO tablename (column1, column2,....)
VALUES (value1, value2,....)
But I have no idea how to insert a recordset.
Secondly, you might need to replace each of your single quotes with a pair of double quotes (can't remember if ADO handles that for you - Access certainly uses double quotes natively).
I now have the following code. I have added the INSERT statement to my CmdSearch_Click() so it copies the results stright into the temp table.
I get no error messages the data just doesn't appear in the table. Can someone tell me where I am going wrong?
VB Code:
Private Sub CmdSearch_Click()
Dim strSQL As String, strWhere As String
strSQL = "SELECT * FROM Asbestos"
If Text1.Text <> vbNullString Then
strWhere = strWhere & " AND [Sample Number] LIKE ""%" & Text1.Text & "%"""
End If
If Text2.Text <> vbNullString Then
strWhere = strWhere & " AND [Unit Number] LIKE ""%" & Text2.Text & "%"""
End If
If Text3.Text <> vbNullString Then
strWhere = strWhere & " AND [Product Type] LIKE ""%" & Text3.Text & "%"""
End If
If Text4.Text <> vbNullString Then
strWhere = strWhere & " AND [Asbestos Type] LIKE ""%" & Text4.Text & "%"""
End If
If Text5.Text <> vbNullString Then
strWhere = strWhere & " AND [Location In Unit] LIKE ""%" & Text5.Text & "%"""
End If
If Text6.Text <> vbNullString Then
strWhere = strWhere & " AND [PBC Job Number] LIKE ""%" & Text6.Text & "%"""
End If
If Text7.Text <> vbNullString Then
strWhere = strWhere & " AND [Work Done] LIKE ""%" & Text7.Text & "%"""
End If
If Text8.Text <> vbNullString Then
strWhere = strWhere & " AND [Date Of Completion] LIKE ""%" & Text8.Text & "%"""
End If
If Text9.Text <> vbNullString Then
strWhere = strWhere & " AND [Air Test Certificate Number] LIKE ""%" & Text9.Text & "%"""
End If
If Text10.Text <> vbNullString Then
strWhere = strWhere & " AND [Assessment Score - Material] LIKE ""%" & Text10.Text & "%"""
End If
If Text11.Text <> vbNullString Then
strWhere = strWhere & " AND [Assessment Score - Priority] LIKE ""%" & Text11.Text & "%"""
End If
If Text12.Text <> vbNullString Then
strWhere = strWhere & " AND [Assessment Score - Total] LIKE ""%" & Text12.Text & "%"""
That will probably be because you are trying to stuff a string into a numeric field. Check all the data types of your database columns, and if any of them are numeric, remove the surrounding quotes in the INSERT string.
[edit]
Oh, hang on. "Criteria expression" implies a WHERE clause, and you don't have one... which cn.Execute(strSQL) is failing - DoInsert or GetRecordset?
[/edit]
Just curious - how complex a report are you doing that you are going through all this effort to move data into ACCESS into order to do it. That seems to me like hitting a nail with a sledgehammer!
The PRINTER object in VB allows for FONTSIZE and FONTSTYLE changes - so you can do large font headings - bold, italics - all with one line commands...
Looping through the rows and columns of a flexgrid to print is very, very easy.
*** Read the sticky in the DB forum about how to get your question answered quickly!! ***
Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".
strWhere = strWhere & " AND [Sample Number] LIKE ""%" & Text1.Text & "%"""
End If
If Text2.Text <> vbNullString Then
strWhere = strWhere & " AND [Unit Number] LIKE ""%" & Text2.Text & "%"""
End If
If Text3.Text <> vbNullString Then
strWhere = strWhere & " AND [Product Type] LIKE ""%" & Text3.Text & "%"""
End If
If Text4.Text <> vbNullString Then
strWhere = strWhere & " AND [Asbestos Type] LIKE ""%" & Text4.Text & "%"""
End If
If Text5.Text <> vbNullString Then
strWhere = strWhere & " AND [Location In Unit] LIKE ""%" & Text5.Text & "%"""
End If
If Text6.Text <> vbNullString Then
strWhere = strWhere & " AND [PBC Job Number] LIKE ""%" & Text6.Text & "%"""
End If
If Text7.Text <> vbNullString Then
strWhere = strWhere & " AND [Work Done] LIKE ""%" & Text7.Text & "%"""
End If
If Text8.Text <> vbNullString Then
strWhere = strWhere & " AND [Date Of Completion] LIKE ""%" & Text8.Text & "%"""
End If
If Text9.Text <> vbNullString Then
strWhere = strWhere & " AND [Air Test Certificate Number] LIKE ""%" & Text9.Text & "%"""
End If
If Text10.Text <> vbNullString Then
strWhere = strWhere & " AND [Assessment Score - Material] LIKE ""%" & Text10.Text & "%"""
End If
If Text11.Text <> vbNullString Then
strWhere = strWhere & " AND [Assessment Score - Priority] LIKE ""%" & Text11.Text & "%"""
End If
If Text12.Text <> vbNullString Then
strWhere = strWhere & " AND [Assessment Score - Total] LIKE ""%" & Text12.Text & "%"""
End If
If strWhere <> vbNullString Then
strSQL = strSQL & " WHERE " & Mid$(strWhere, 5)
End If
'fill the grid
LoadTheGrid (strSQL)
End Sub
Private Sub Form5_Unload(Cancel As Integer)
'
End Sub
I've rejigged it a bit as well - just my personal taste.
What I was trying to explain earlier was that when you are constructing your INSERT string, rsSearch wasn't 'visible' to your code. By moving it into the LoadTheGrid method, you can fire the INSERT for each row you return for display in the grid.
[BTW I agree with szalamany, this does seem rather a lot of work for little gain - there are several other ways to achieve this result.]
The results are now added to the Asbestos Temp table when I click search.
How can I delete the contents of the table when I click the back button?
Also, how can I open a report from access in VB?
Aha! Now you see why szalamany was suggesting you print with the Printer object. Opening Access reports from within VB can get a bit ugly. There are plenty of threads on these boards that discuss it, e.g. recently this one
(This is the search I did.)
Delete SQL is pretty straightforward:
Code:
DELETE From [Asbestos Temp]
DELETE From [Asbestos Temp] WHERE criteriaString
Reply With Quote
Originally Posted by hscott
