Prob with mainform cbo & subform

[Guest]

Hi

I have a form (frm_RECEIPTS) which records a cash receipts transaction. The
subform (sbfrm_RECDETAILS) lists invoices paid. Both forms linked by TRANS_NO.

I have a combo box (cbo_DEBTOR) on frm_RECEIPTS, which controls a combo box
(cbo_REF) on the subform using the following code in the AfterUpdate event:

Forms!frm_RECEIPTS.sbfrm_RECDETAILS.Form!cbo_REF.RowSource = "SELECT
MST_TRANS_IDX, MSTRACC_REF AS Ref, TRANS_DATE As Date, AMOUNT As Amount,
OUTSTANDING As Outstanding, NARRATIVE, TYPE, DEBTOR_IDX FROM tbl_MSTRACC
WHERE (TYPE = 'INV' OR TYPE = 'CRE') AND OUTSTANDING <> 0 AND DEBTOR_IDX = "
& Me!DEBTOR_IDX

Forms!frm_RECEIPTS.sbfrm_RECDETAILS.Form!cbo_REF.Requery

This seems to work fine as cbo_REF is showing the correct invoices according
to whichever Debtor is chosen in cbo_DEBTOR.

My problem is that whenever I open the form, the subform remains blank until
I rechoose the debtor (which does show correctly when the form is opened),
and then the invoice details appear. When I move to the next record, again
the subform is blank until I rechoose the debtor, and the invoices lines
reappear. However, going back to the first record, the subform is blank
again. I can't seem to get the subform details to appear and remain.

Can anyone help me here? Detailed help would be good as I'm a novice at
Access.

Thanking you in advance
Winsa

 

[Steve Schapel]

Winsa,

You are using code to manipulate the Row Source of the cbo_REF combobox
on the subform. This is quite a complicated way of achieving a simple
purpose, and the question remains as to what, if anything, is the Row
Source of the combobox before a selection is made in the cbo_DEBTOR
combobox. I would just make a query to use as the Row Source for the
cbo_REF combobox, and use the reference to the main form control (I
couldn't see where DEBTOR_IDX came into the picture) in the criteria of
the query. Then you don't need all that code.

But anyway, it sems to me that this is probably not related to the
problem you have outlined. Do you have some other code somewhere that
is manipulating the Record Source property of the subform, or the Source
Object property of the subform control? Maybe some code on the Current
event of the main form?

 

[Steve Schapel]

Winsa,

Ah ok, maybe this is it... Maybe you are using the cbo_REF control in
the Link Child Fields property setting of the subform. And maybe there
is no Row Source for the combobox at all until it is assigned by the
code on After Update of the cbo_DEBTOR combobox, so therefore can not
display a value when the form opens at a new record. Does this sound
like it might be the case? If so, my earlier suggestion about the query
might help.

 

[Guest]

Hi Steve

Thanks for your suggestions.

Firstly, the rowsource of cbo_DEBTOR is another query based on Debtor Number
and Name, where Debtor Number is the bound column. From this, cbo_REF should
show only those invoices that belong to the chosen Debtor Number. This is
where the DEBTOR_IDX (Debtor Number) comes in.

Do you have some other code somewhere that is manipulating the Record Source property of the subform, or the Source Object property of the subform control?

I'm not sure I understand you correctly, but I don't have any other code
manipulating the Record Source of the subform. There is a Record Source that
is a query based on the underlying table for the subform if that is what you
mean?

No, I'm not using cbo_REF control in the Link Childs Field property. I
tried that, but Access told me that I was using an invalid column. The link
field is the Receipt Transaction Number (TRANS_NO).

But yes, I think you are correct in that there may be no rowsource for
cbo_REF until cbo_DEBTOR is assigned, therefore I'm not getting any data
until that is assigned.

If I understand correctly what you are saying, that I just use a query for
cbo_REF, and then in the Link fields property, I enter DEBTOR_IDX instead of
TRANS_NO, will that give me the results I'm after? Therefore the query that
I have in the AfterUpdate Event should just go straight into the recordsource
of cbo_REF and hopefully that will fix it?!?!

Winsa

 

[Guest]

Forgot to add, could it be a problem that DEBTOR_IDX is not actually a field
in the subform?? Perhaps this is where I'm getting the error when I try to
use it as a link field???

Winsa

 

[Steve Schapel]

Winsa,

Well, there is a princple that needs to be considered here. There is a
relationship between the data in the main form's record source, and the
data in the subform's record source. That relationship is based on the
two sets of data having a field or fields in common. This field is what
you must use as the Link Master Fields and Link Child Fields properties
of the subform. On the basis of what you have told us so far, I don't
know what fields these are, but you need to work it out on this basis,
there's not really any flexibility in this.

And yes, the subform will need to have a record source (table or query)
assigned which will return the correct records at the time that the form
is opened, and at the time that you navigate from one main form record
to another. And similarly, the cbo_REF combobox will need to have a row
source assigned which will return the correct list at the time that the
form is opened, and at the time that you navigate from one main form
record to another. If you are assigning the row source of the combobox
in code on a main form event, you have to consider what the row source
is before the code assigns/modifies it. That's why I was suggesting
using a query for the combobox's row source, rather than coded SQL.

 

[Guest]

Hi Steve

I sort of understand what you are saying. I understand that the master and
child need a link field, and I have that, but it's not related to the Debtor
field. I'm only trying to use that field from the main form to show the
correct invoices in the combo on the subform. Storing the Debtor in the
subform is redundant, that's why the subform does not have this field.

I tried just using a normal query for the subform combo box, but it then
shows up all invoices, not just the ones related to the Debtor chosen on the
main form.

I have no idea where to go from here, or even what I should be considering.

Thanks
Winsa.

 

[Steve Schapel]

Winsa,

Can you post back with the SQL view of the "normal query" that you are
trying for the subform combobox's row source?

 

[Guest]

Hi Steve

Here is the SQL of the "normal query". It's pretty much just the same as
what I posted before, but just not in code.

SELECT MST_TRANS_IDX, MSTRACC_REF AS Ref, TRANS_DATE As Date, AMOUNT As
Amount, OUTSTANDING As Outstanding, NARRATIVE, TYPE, DEBTOR_IDX
FROM tbl_MSTRACC
WHERE (TYPE = 'INV' OR TYPE = 'CRE') AND OUTSTANDING <> 0

I'm not sure how to include the DEBTOR_IDX in the WHERE clause. I don't
know how to reference a control on a form in SQL, if you can do that at all.

This query just gives me the list of all invoices, not just the ones
pertaining to the chosen debtor in the main form.

Thanks heaps for your help!
Winsa

 

[Steve Schapel]

Winsa,

SELECT MST_TRANS_IDX, MSTRACC_REF AS Ref, TRANS_DATE, AMOUNT,
OUTSTANDING, NARRATIVE, TYPE, DEBTOR_IDX
FROM tbl_MSTRACC
WHERE (TYPE = 'INV' OR TYPE = 'CRE') AND OUTSTANDING <> 0 AND DEBTOR_IDX
= [Forms]![frm_RECEIPTS]![DEBTOR_IDX]

 

[Guest]

Hi Steve

That's what I thought the SQL would be, but I get errors,

Error in WHERE clause near '!'
Unable to parse query text

Is this because I'm working with an adp and not a normal mdb??

I tried (DEBTOR_IDX = '[Forms]![RECEIPTS]![DEBTOR_IDX]'), but it wouldn't
show anything.

Any other ideas?

Thanks
Winsa

Winsa,

SELECT MST_TRANS_IDX, MSTRACC_REF AS Ref, TRANS_DATE, AMOUNT,
OUTSTANDING, NARRATIVE, TYPE, DEBTOR_IDX
FROM tbl_MSTRACC
WHERE (TYPE = 'INV' OR TYPE = 'CRE') AND OUTSTANDING <> 0 AND DEBTOR_IDX
= [Forms]![frm_RECEIPTS]![DEBTOR_IDX]

--
Steve Schapel, Microsoft Access MVP

Hi Steve

Here is the SQL of the "normal query". It's pretty much just the same as
what I posted before, but just not in code.

SELECT MST_TRANS_IDX, MSTRACC_REF AS Ref, TRANS_DATE As Date, AMOUNT As
Amount, OUTSTANDING As Outstanding, NARRATIVE, TYPE, DEBTOR_IDX
FROM tbl_MSTRACC
WHERE (TYPE = 'INV' OR TYPE = 'CRE') AND OUTSTANDING <> 0

I'm not sure how to include the DEBTOR_IDX in the WHERE clause. I don't
know how to reference a control on a form in SQL, if you can do that at all.

This query just gives me the list of all invoices, not just the ones
pertaining to the chosen debtor in the main form.

Thanks heaps for your help!
Winsa

 

[Steve Schapel]

Winsa,

When do you get an error? What happens when you try to open the query
datasheet? Can you copy/paste the exact SQL from the query that causes
the error?

 

[Guest]

Hi Steve

Sorry for the delayed reply, I haven't been at work for a few days.

I entered in the SQL exactly as you had suggested in the Record Source Build
Code section of cbo_REF. When I went to run the query, by clicking on "!",
it came up with the error (I put the whole message in quotes):

" SQL Syntax Error Encountered

The following errors were encountered whilst parsing the contents of the SQL
pane:

Error in WHERE clause near '!'.
Unable to parse query text. "

and Access changed the SQL to read:

SELECT MST_TRANS_IDX, MSTRACC_REF AS Ref, TRANS_DATE, AMOUNT, OUTSTANDING,
NARRATIVE, TYPE, DEBTOR_IDX
FROM tbl_MSTRACC
WHERE (TYPE = 'INV' OR
TYPE = 'CRE') AND OUTSTANDING <> 0 AND DEBTOR_IDX = [Forms] !
[frm_RECEIPTS] ! [DEBTOR_IDX]

Anymore ideas?

Thanks for all your help.

Winsa

Winsa,

When do you get an error? What happens when you try to open the query
datasheet? Can you copy/paste the exact SQL from the query that causes
the error?

--
Steve Schapel, Microsoft Access MVP

Hi Steve

That's what I thought the SQL would be, but I get errors,

Error in WHERE clause near '!'
Unable to parse query text

Is this because I'm working with an adp and not a normal mdb??

I tried (DEBTOR_IDX = '[Forms]![RECEIPTS]![DEBTOR_IDX]'), but it wouldn't
show anything.

Any other ideas?

Thanks
Winsa

 

[Steve Schapel]

Winsa,

I guess you mean the Row Source not Record Source?

My first guess is that you have copied the line returns in the SQL that
were put into my post by your newsreader.

Why not try to make the query in query design view? Make a query based
on the tbl_MSTRACC table, add the MST_TRANS_IDX, MSTRACC_REF AS Ref,
TRANS_DATE, AMOUNT, OUTSTANDING, NARRATIVE, TYPE, DEBTOR_IDX fields to
the query design grid, and put your Criteria in as follows:
In the criteria of the TYPE field put...
"INV" Or "CRE"
In the Criteria of the OUTSTANDING field put...
<>0
And in the criteria of the DEBTOR_IDX field put...
[Forms]![frm_RECEIPTS]![DEBTOR_IDX]

See if that works. You can then see the SQL view of this query via the
View menu.

 

[Guest]

Hi Steve

Yes, sorry I did meant Row Source, not Record Source!

I tried creating the query from scratch, and this is the SQL I got:

SELECT MST_TRANS_IDX, MSTRACC_REF, TRANS_DATE, AMOUNT, OUTSTANDING,
NARRATIVE, TYPE, DEBTOR_IDX
FROM dbo.TESTMSTRACC
WHERE (TYPE = 'INV' OR
TYPE = 'CRE') AND (OUTSTANDING <> 0) AND (DEBTOR_IDX =
'[Forms]!frm_RECEIPTS!DEBTOR_IDX')

Unfortunately this retrieved nothing at all!! Both the query I tried and
the cbo_REF comes up empty. All that cbo_REF contains is the column headings
and nothing else.

I have found another way to make it work, albeit more of a long winded way.
I'm using the same code to populate the Row Source of cbo_REF in the ON
CURRENT event of the main form as well as in the AFTER UPDATE Event of
cbo_DEBTOR. This seems to give me the desired results.

Thanks heaps for your suggestions and much valued help. Pity it wouldn't
work for whatever reason!

Thanks again!
Winsa

Winsa,

I guess you mean the Row Source not Record Source?

My first guess is that you have copied the line returns in the SQL that
were put into my post by your newsreader.

Why not try to make the query in query design view? Make a query based
on the tbl_MSTRACC table, add the MST_TRANS_IDX, MSTRACC_REF AS Ref,
TRANS_DATE, AMOUNT, OUTSTANDING, NARRATIVE, TYPE, DEBTOR_IDX fields to
the query design grid, and put your Criteria in as follows:
In the criteria of the TYPE field put...
"INV" Or "CRE"
In the Criteria of the OUTSTANDING field put...
<>0
And in the criteria of the DEBTOR_IDX field put...
[Forms]![frm_RECEIPTS]![DEBTOR_IDX]

See if that works. You can then see the SQL view of this query via the
View menu.

--
Steve Schapel, Microsoft Access MVP

Hi Steve

Sorry for the delayed reply, I haven't been at work for a few days.

I entered in the SQL exactly as you had suggested in the Record Source Build
Code section of cbo_REF. When I went to run the query, by clicking on "!",
it came up with the error (I put the whole message in quotes):

" SQL Syntax Error Encountered

The following errors were encountered whilst parsing the contents of the SQL
pane:

Error in WHERE clause near '!'.
Unable to parse query text. "

and Access changed the SQL to read:

SELECT MST_TRANS_IDX, MSTRACC_REF AS Ref, TRANS_DATE, AMOUNT, OUTSTANDING,
NARRATIVE, TYPE, DEBTOR_IDX
FROM tbl_MSTRACC
WHERE (TYPE = 'INV' OR
TYPE = 'CRE') AND OUTSTANDING <> 0 AND DEBTOR_IDX = [Forms] !
[frm_RECEIPTS] ! [DEBTOR_IDX]

Anymore ideas?

Thanks for all your help.

Winsa