Subform RecordSource Error 3008

[thelonelyghost]

Just searched google and this website but I couldn't come up with an answer to this.

Basic Information
Software: Microsoft Access 2000
OS: Windows XP Professional SP3
Problem: Error 3008 (locked table, [tblReturnLog]) when trying to modify recordsource of subform.

Private Sub btnSearch_Click()

'Changes recordsource of subform. subform name: frmSubform
' subform source form: [frmTEMP2]
Me.frmSubForm.Form.RecordSource = BuildSQLStr

End Sub

BuildSQLStr is a function that I've debugged and it's working correctly. It outputs a string containing a SELECT statement with WHERE conditions. Before anyone objects, I need it to be formatted this way (as opposed to using filters) to address potential bandwidth issues.

What am I doing wrong? The parent form, a filter form, is bound to a different table and only a combo box reads the field names of the locked table, thereby enabling an ORDER BY option for the results of the filter. Let me know if there's any more info that you need.

[NeoPa]

Can we see the SQL string created that fails?

[thelonelyghost]

it's a function to dynamically create a string of SQL code. It starts by querying each control if it is null (so as to exclude ones that are), then runs the info (fieldname, control location, AND/OR separator) through another function based on if it should be LIKE, =, >=, or <=. That function then generates code like this:

" AND [Mileage] >= 44896"

The above string is concatenated with similar strings based on the controls already set filled in, all into one string. That one string, strSQLContent, is stripped of the initial AND/OR operator and replaced with "WHERE " so that it may look like this:

"WHERE [Mileage] >= 44896 AND [Customer] LIKE 'Dra%%y' AND [ReturnType] = Engineering"

That string is then concatenated with:

"SELECT * FROM [tblReturnLog] " & strSQLContent & " ORDER BY [" & Me.cmbGroupBy & "]"

Where cmbGroupBy is a combo box filled with field names in [tblReturnLog]. The output of BuildSQLStr in this case would look like this:

"SELECT * FROM [tblReturnLog] WHERE [Mileage] >= 44896 AND [Customer] LIKE 'Dra%%y' AND [ReturnType] = Engineering ORDER BY [intCTSLogNumber]"

Any further questions?

EDIT: I neglected to mention that the trial I ran was a simpler string. It was "SELECT * FROM [tblReturnLog] WHERE [intCTSLogNumber] = 6 ORDER BY [intCTSLogNumber]" and it should have only shown one record, since that field is the primary key in the table I'm attempting to query.

[patjones]

Happy that you made it past the initial error in the other thread.

Here, what do you mean by [ReturnType] = Engineering?

Pat

[thelonelyghost]

Happy that you made it past the initial error in the other thread.

Here, what do you mean by [ReturnType] = Engineering?

Pat

[ReturnType] is a field representing the place an item was returned from. The only values it can be are one-word values such as 'Field', 'Plant', 'Engineering', etc. There aren't quotes since they were extraneous characters. The '=' is used instead of 'LIKE' because the input is from a combo box. No room for misspellings.

EDIT: Just revisited the thread you mentioned and realized I forgot to name you as the 'Best Answer' again. I felt that issue was sufficiently solved and then some. :)

[patjones]

There have to be quotes though...

"WHERE [Mileage] >= 44896 AND [Customer] LIKE 'Dra%%y' AND [ReturnType] = 'Engineering'"

[thelonelyghost]

There have to be quotes though...

"WHERE [Mileage] >= 44896 AND [Customer] LIKE 'Dra%%y' AND [ReturnType] = 'Engineering'"

Okay, then I'll change the function that controls it. Although I appreciate the proofreading, that isn't the cause of the issue. It's saying the table is locked by something else, before it even gets a chance to process the statement further. Perhaps breaking the sequence at 'FROM'(?)

[patjones]

Just searched google and this website but I couldn't come up with an answer to this.

Basic Information
Software: Microsoft Access 2000
OS: Windows XP Professional SP3
Problem: Error 3008 (locked table, [tblReturnLog]) when trying to modify recordsource of subform.

Private Sub btnSearch_Click()

'Changes recordsource of subform. subform name: frmSubform
' subform source form: [frmTEMP2]
Me.frmSubForm.Form.RecordSource = BuildSQLStr

End Sub

BuildSQLStr is a function that I've debugged and it's working correctly. It outputs a string containing a SELECT statement with WHERE conditions. Before anyone objects, I need it to be formatted this way (as opposed to using filters) to address potential bandwidth issues.

What am I doing wrong? The parent form, a filter form, is bound to a different table and only a combo box reads the field names of the locked table, thereby enabling an ORDER BY option for the results of the filter. Let me know if there's any more info that you need.

I would say that you should check the record-locking state of the parent form anyway, even if it is only the combo box that is sharing the locked table.

Pat

[thelonelyghost]

I would say that you should check the record-locking state of the parent form anyway, even if it is only the combo box that is sharing the locked table.

Pat

How would I do that? I don't see it in the context menu for the combo box, nor do I see it on the context menu for the form. BTW, sorry for snapping like that, zepphead80. I'm just sick of hitting all of these obstacles on my project.

[patjones]

How would I do that? I don't see it in the context menu for the combo box, nor do I see it on the context menu for the form. BTW, sorry for snapping like that, zepphead80. I'm just sick of hitting all of these obstacles on my project.

It's not a problem at all. Anyone who works with Access long enough has days like this, believe me.

If you open the parent form in design view and look at it's Data tab in the property sheet, you'll find various settings relating to it's recordsource. Record Locks should be in there somewhere. My disclaimer is that most of my experience is with Access 2007...so hopefully things haven't changed much.

There are other settings relating to the form's recordset, such as Recordset Type, Data Entry, and various settings that determine whether additions, edits and deletions are allowed. Those probably aren't causing your problem now, but I'd still look at Record Locks and see what it's doing.

My reasoning is that if the combo box on the parent form is accessing that table, it could be creating a conflict with your desire to base the subform off that table.

Pat

[thelonelyghost]

Although the parent form had record locks turned off, the child form didn't. I switched that, but I found that my SQL query doesn't work at all now. I thought I read somewhere that it was supposed to requery automatically.. .

At any rate, the issue I started this thread about is resolved now. Thanks for your patience!

I know you've been following me zepphead80, so do you have any clue what's wrong? I've got the source code on scattered throughout my various threads (all three of them) if you need a refresher.

[patjones]

How do you know it isn't working? Are you getting another error message or is it just opening and not doing anything?

[thelonelyghost]

How do you know it isn't working? Are you getting another error message or is it just opening and not doing anything?

The btnSearch button isn't doing anything. When I try to click on the subform, it comes up with a warning saying "intMake cannot be zero-length", despite every indication it can be. The only reasons I see it's intMake that reacted are because of the [Event Procedure] (simple cascading combo box between make and model), and the recordsource of intMake ("SELECT [tlkpMake].[MakeID], [tlkpMake].[MakeName] FROM tlkpMake; " verbatim from properties)

EDIT: I also decided to upload a screenshot of the form I'm working on. There's no visible sensitive information so no worries there.

[patjones]

This is where those other properties on the property sheet enter into the picture, the Data Entry property being the key one.

When Data Entry is set to "No", the form will display records normally, and you'll be able to navigate amongst the records. The entries for Allow Edits, Deletions, etc. determine the extent to which you can make changes to the recordset on which the bound form is based when Data Entry is set to "No".

When Data Entry is set to "Yes", the form opens with all the boxes blank...which allows you to enter a new record. However you will not be able to view existing records.

For unbound forms that you populate via DAO or ADO, these various properties have no meaning.

Pat

[thelonelyghost]

This is where those other properties on the property sheet enter into the picture, the Data Entry property being the key one.

When Data Entry is set to "No", the form will display records normally, and you'll be able to navigate amongst the records. The entries for Allow Edits, Deletions, etc. determine the extent to which you can make changes to the recordset on which the bound form is based when Data Entry is set to "No".

When Data Entry is set to "Yes", the form opens with all the boxes blank...which allows you to enter a new record. However you will not be able to view existing records.

For unbound forms that you populate via DAO or ADO, these various properties have no meaning.

Pat

I guess I don't understand what you're saying. The box at the bottom of the screenshot is only meant to act as a viewing window for company records, not to edit them. I just made a subform and viewed it as a datasheet, with a recordsource being [tblReturnLog]. All data entry happens on the fields above, and only entering it into a sort of search query. As outlined earlier with the EqualsAttachAnd and BuildSQLStr issues, the search query is built into a SELECT WHERE statement.

In relation to what you were saying, I checked if the subform had Data Entry set to Yes, but it was actually set to No. Once again, the subform is only meant to act as a viewing window for [tblReturnLog]. Do you have a suggested alternative for viewing the records? I only needed the SQL code from BuildSQLStr to be in that form for ease of transition to printing a report and so that it may minimize bandwidth, assuming [tblReturnLog] is ever remotely hosted.