Main Form Sub Form Problem

[jmawebco]

I have a main form which displays data based on selections made by
user. Form load initially with no data in it. I have a sub form that is
supposed to be populated once a selection is made on the main form. The
problem I am having is that nothing is being displayed in the sub form.
No error messages are received and debugging shows values assigned to
combo box are correct.

I set the sub form record source during the Current event of the main
form and refresh it after a selection is made on the main form. The
initial record source selects all records from a query then when the
user selected an item from a combo box and the After Update event fires
a requery takes place of the same query but with filtering based on the
selection.

I sure hope someone can point me in the right direction here, I've been
at this for days and have made very little progress.

 

[Guest]

How are you setting the subform record source to the filtered recordset, i.e.
setting new value as SQL string in RecordSource property or applying Filter
or ServerFilter property?

 

[Guest]

Signing off now ('cos its midnight in UK) - reasoning behind my question is
that if you are generating an SQL that is greater than 255 characters, the
RecordSource property won't see it all and won't return an error, just an
empty recordset and consequently and empty form. Try generating just the
Where part of the SQL and passing it to the Filter property (or ServetFilter
if using an Access Project with SQL Server. That should work.

 

[jmawebco]

Since I am a bit new at this how about you give me a sample of what you
mean. My sql statement is as follows;

newSql = "SELECT Trim(PR_EMPLOYEE_MASTER_1_MC!Employee_Code) AS
Employee_Lookup, " & _
"PR_EMPLOYEE_MASTER_1_MC.Employee_Code,
PR_EMPLOYEE_MASTER_1_MC.Employee_Name, " & _
"Trim([Last_Job_Number]) AS jobnumber,
PR_EMPLOYEE_MASTER_1_MC.Selected_Flag " & _
"FROM PR_EMPLOYEE_MASTER_1_MC " & _
"WHERE (((Trim([Last_Job_Number]))=" &
"""Me!cbo_Job_Number.Column(0)""" & ")" & _
"AND ((Len(Trim([PR_EMPLOYEE_MASTER_1_MC]![Last_Job_Number])))>0));"

Please let me know if you see anything wrong here.

 

[Guest]

Hi - sorry about the delay, I've been ill the last few days and definitely
"off line".

Your SQL statement is over 300 characters long. As I wrote earlier, the
RecordSource property of a form only holds 255 characters, so its getting
truncated and threrefore returns an empty set of records. Take the basic
select statement (i.e everything up to the WHERE keyword, but not including
"WHERE") and use that to create a stored query in the database, let's call it
MyRecords. Then you alter the code from your app. to read:

newSQL = "Select *From MyRecords " _
& " "WHERE Trim([Last_Job_Number]) = " _
& "'" & Me!cbo_Job_Number.Column(0) & "'" _
& " AND Len(Trim([PR_EMPLOYEE_MASTER_1_MC]![Last_Job_Number])) > 0;"

Tht will reduce the overall SQL to less than 255 characters. Your syntax
also shows a copule of errors in the way the combo box value is included
(note that changes in my copy) as well as lack of a space characters right
before the "AND" keyword. Try altering as above and you should be OK

HTH