Replies

Cool! It is now working.

The thing I needed was the syntax for referring to the value in the input box in the SQLstring line, which is

SQLstring = “SELECT * FROM CRCG_Contacts WHERE HHSC_Regions = ‘” & HHSC_Regions & “‘;”
with the single quote – double quote – ampersands bracketing the field name.

I will probably not implement this as a form submitting to itself, since I needed to get user input to filter the Access db based on selections; I think what I’ll do is assemble a ‘Search the DB’ page with various options (search by a CRCG Type, County or Region, etc.) that goes to a single Search Results page… currently there are 3 identical .asp pages with slight differences (hard-coded) in the SQLstring. I’d obviously be better off with one.

That way I don’t have to worry about point no. 1. as for point 3, i had at some point in my troubleshooting process deleted the submit button (don’t ask me why, it was getting late) – since then i put one in and, with the bit of coding you favored me with, it works as needed.

Also, since i am going to have several SQLstring criteria pulled together on a search page, I will need to use a wildcard character for some conditions which will expect filtering on the generic .asp page. I assume that would be the asterisk [*] in the form value?

There are some othe issues that I will likely have to look into, but it’s looking better than before.

Thanks again!

I might try to get .asp to generate the .html table code, but for now that is not so important. it works as it is.

as far as grabbing the user input, i have one form that i am developing at the moment that is more or less a ‘search results’ page, in that the form POSTs to itself to display the data from the db. what i have so far doesn’t work.

I set up a short form (one inbox and a POST to the form itself:

HHSC_Region

<input TYPE="TEXT" NAME="HHSC_Regions" VALUE="” size=”20″>

And then I set up a connection to access and a SQLstring that I want to pull from whatever is inputted above:

<%
'– Declare your variables
Dim DataConnection, SQLstring, myRecordSet, DBFileName, DBmyRecordSetFileName, Value, HHSC_Regions
' Change the db1.mdb to .mdb
DBFileName = “WebUpdateLocalContacts.mdb”

‘– Create dataconnection and recordset object and open database
Set DataConnection = Server.CreateObject(“ADODB.Connection”)
Set myRecordSet = Server.CreateObject(“ADODB.Recordset”)

DataConnection.Open “DBQ=” & Server.Mappath(DBFileName) &”;Driver={Microsoft Access Driver (*.mdb)};”

‘– default SQL
‘ Change the SQL string to a SQL string for your DB
SQLstring = “SELECT * FROM CRCG_Contacts WHERE CRCGType = ‘Children’ AND HHSC_Regions = ‘HHSC_Regions’;”
‘SQLstring = SQLstring & “HHSC_Regions = ‘HHSC_Regions’;”

myRecordSet.Open SQLstring, DataConnection

%>

Now, possibly if I hard-coded the HHSC_Regions to be a number (1 thru 11) this would if a SELECT CASE based on the input was installed, that might work (it’s been recommended in some tip sites) but I don’t think that solution is particularly elegant . Again, I suspect the problem is relatively easy but i have been futzing with it for a while…

I have other pages that will take an input on that page and then open the search pages with the specified criteria, as you suggested, but I don’t see why you can’t have the asp page post to itself, as a search results page does. And I can get this scenario to work using FP xsions, but I am trying to keep my site pure of FP xsions….

Any ideas? I certainly appreciate your help!

thanks for the response but i figured out what to do in the meantime. it was pretty simple, as I thought; just needed to figure out how to work the code.

here’s my solution (it’s generic enough for most applications):

At the beginning of pulling the data into a table do this:

—- table block

well that’s a lot of code!

it looks like you are using ADO to create a recordset space and then map data elements from the form to the fields in a recordset “Events” which is then populated with data from the form via Request.Form — and this is NOTHING LIKE the code i am staring at. oy gevalt—

the method the outsourced vendor chose was the execute a SQL Query in ASP and zap it to SQL Server, and I have indeed reason to suspect we have been loosing records, if that’s what you mean by ‘little data verification control using web pages for capturing input. ‘ (You could mean not having control over whether a field is filled in or in the right format, etc.) But I don’t think it is appropriate to re-write the entire code DB just to migrate to Access — I wanted to port it to Access and thought perhaps connectivity was the issue, not the ASP method itself. it’s starting to look like it’s not feasable.

However, I do have other user-input forms that could easily be converted from form-to-email to access db and your codeing samples should help in implementing that.

Well, I thank you very much for your help and advice!

Thanks for your help. I have tried a few other things since you responded:
1) Set an item in the table to be updated as a primary key
2) Using IIS, enabled the Access file to be read and written to and also allowed ‘Script Source Access’

Neither of these helped either.

I am now wondering whether the problem is not so much establishing a connection (I think that I have gotten that far) but in verifying the ASP/SQL code is building a QUERY that can INSERT into the access db (i capitalize these words b/cuz they are in the actual code). After all, the ASP/SQL query was written for posting to SQL Server, so perhaps there are enough variants that Access is getting confused and telling me something not altogether accurate.

So — if that’s the case — then I will have to re-write a query/submit in ASP that Access understands. This is all very confusing, as Access understands VB natively and also supports SQL scripting but I don’t know how to get a data element from a webpage to get written into Access, basically — I don’t suppose VB has anything to do with all this, but there is an extensive help file in Access for it…

The queries themselves are not *particulaly* difficult but they are long. Perhaps I need an example of a simple query that Access can understand to help resolve this problem.

I’ve also used higher versions of the jet db in the connect string with similar results.

well thanks anyway for your quick response!

well, according to MSKB article here http://support.microsoft.com/directory/art…B;EN-US;q193332, if you look in the section ‘Setting up a DSN-Less Connection’, there are connect strings for Access, SQL Server and Oracle, all of which specify PROVIDER as MSDASQL and in the DRIVER section of the connect string it is specified which *kind* of SQL source is being called. If I use ASP Express to put together a DSN-Less connect string it does the same thing. According to the KB, MSDASQL is the ‘default OLE DB provider for ADO’, and some people just omit it completely; including it in the string is considered best practice.

And like I said, it seems to be able to open a connection but crashes when it tries to insert records into a table (it says queries must be ‘updateable’, whatever that means). If I substitute MSDASQL with this:

“PROVIDER=Microsoft.Jet.OLEDB.3.51;”

It crashes on connect with error ‘this provider cannot be found’.

So I am thinking the connection might be alright, at least as far as it finding and opening the db, but something is not allowing the data to be written into the db.

thanks for the tip. i have noticed quirky loading on some of my db’s, even tho one is essentially a copy of the other with new data put in. and that one uses forms and subforms with combo boxes in several places. but it’s never been bad enough for me to need to fix it.

i almost never use the datasheet view either, as i find it a little confusing…I kind of like GUIs.

There was no error number on the message box. Just the triangular exclamation point icon and an ‘OK’ button.

However, I may have fixed the problem just recently. I was trying to open a report based on the Form/Subform information and re-directed the report to pull data from a query with the tables for the Form and Subform linked up. Now it (the report) behaves properly. I also no longer get the “Out of String Space” error in the form with the subform and am able to add and subtract and change video titles willy-nilly!

Not that this really clarifies what the error message was… Very mysterious. This was one of those cases of mucking about almost at random and fixing the problem without ever really knowing what the problem was.

As far as the issue of ‘string space’, well, this is Access 2000 on Windows 2000 – not 16-bit. And there was no programatic stuff going on that i could image would generate a long string — just trying to select an item in a combo box in a subform. Very straitforward.

And as to what I mean by ‘trying to add records to an existing field’, sorry if i was not specific enough, but basically this is a subform on a form, with a combo box (among other things) on the subform. This will, in Datasheet view, generate a record per Main Form with the little plus icon indicating multiple records of information for the subreport. And since the combo box is on the subform, you can change the name of a title or add a new row to the subform for an additional title. I hope this isn’t bad practice because i do this a lot.

Anyhow, I want to thank you very much for your reponse. The Lounge has not yet failed to provide quick and accurate replies and discussions.

Thanks for all the help everybody – I have what I need by now. It turns out the problem was using a subreport. I did a query linking the Borrower Info to the Videos table and placed the borrower info in the Header of the report and the Videos info in the Detail of the report, and now all is hunky-dory. No need to use a subreport.

Just goes to show – I knew there was an easy solution to this problem but got all side-tracked with subreports. I’d like to thank Rory esp. for his help in slapping together a demo db that addressed the problem.

Thanks! Worked like a charm. I duplicated the action in a macro after using ‘hyperlink’ to search thru the Project Explorer and no problems there. On the nag screen, I tried a couple of things: 1) setting internet securities all the way down and 2) using Sendkeys in the macro to close the dialog box. Neither thing worked.

Ah well. At least I can get the help file to open, so thanks!

I agree with the idea to link the form/subform with a user ID rather than last name, and will make that change. On the subject of not needing a subform, I read that as meaning I should design a form based on a table that includes all the various videos in the collection. I did that initially but thought it would be better in the long run to separate the video table from the borrower table and relate the items in order to make it easier to update the video list as needed. Otherwise, I would have to add additional check box controls for each video on the single form. Overall, I think what i am trying to accomplish via form/subform is not an uncommon situation, and a form/subform works well enough in terms of entering the data. It’s just getting a report that doesn’t print duplicates of the borrower information that I am getting a problem.

The underlying query *does* generate “duplicate” records for a borrower who has more than one video, but that is because it creates a new row for each video, which is not all that suprising — as far as the SQL is concerned, each row is unique. So, the thing I would like to do is do something like On Open (or On Activate), instruct Access to print only *one* instance of a borrower ID. That would solve the problem, and probably it’s a VB solution. Perhaps a counter loop in the ID and an If test for counts greater than 1 then no print or something like that…

Ah well, on to the VB module!

The database won’t zip down to less than 100 k — it’s at 268K. I will attach a word doc with screen shots of the main form and an example of an offending report. btw, since i used a subreport on the report, the beast won’t convert to Word without losing the subreport info. sheesh!

Thanks for your suggestions. I placed the borrower info in the report header and that makes the report format look a little nicer, but… still getting duplicate reports for anyone borrowing more than one video. I have previously done a form without a subform but wanted to use a subform in anticipation that the video inventory will grow and didn’t want to keep re-designing the form with each additional title.

I also looked at the code for a OpenReport and wondered if there is a method for blocking duplicate reports on open or format or something. Here’s the familiar OpenReport VB:

stDocName = “Borrower Receipt”
DoCmd.OpenReport stDocName, acPreview

Is there a VB code for suppressing duplicates at this point?

Many thanks to all who have responded so far!

I tried that but it didn’t do anything significant. I assume by ‘GroupBy’ you are saying to go into the report design mode, click the control item to group by and then do so using the group/sorting toolbar button.

The problem seems to be that the query I have based the report on generates a copy of the customer name for each video title selected. However, for each (duplicate) item, all the video titles are displayed, so what I am thinking I need to do is, on generating the report, supress printing of duplicate names. Perhaps there is a VB solution…