Hi guys
I have a little problem with a filtered report.
OK I have a table with 3 fields,
SERIE is a text field related to another table's primary key
NUMBERis a numeric field and
NUMSP is a text field and the primary key.
I have a data entry form on which a user inputs the SERIE value with a dropdown list. Immediately, the BeforeInsert event of the form sets the NUMBER value to Dmax("NUMBER","TABLE", , ) +1 or something to that effect, and the BeforeUpdate event set the vale of NUMSP to "SP"&[SERIE]&[NUMBER]&Year(Date()) or something to that effect with a macro
OK for now everything on the form works fine.
I have a report which I want to preview and filter so it shows only the current record on the form. This is done by filtering the DoCmd.OpenReport so that the report's NUMBER equals the form's NUMBER . And dit works.
Now, I need to set a criteria to the DMax function, so the DMax searches the highest value where SERIE equals the SERIE value on the form. This is to have the NUMBER incremented by 1 and maintain the sequence for each SERIE value, This of course will generate repeated values of NUMBER, so I cannot use that field in the report filtering. So I want to change the report filtering to the NUMSP field. I will do this when I figure out how to solve the problem, which is...
that if I change the report filtering to [NUMSP] = Me.NUMSP, the report will prompt me for values of "SP" and [SERIE]
I do not know what the problem is since there are no repeated values of NUMSP.
I have tried to explain things as well as I can, have searched the web and the forum for a solution but I haven't found anything similar enough to this issue that will help me.
Any help will be greatly appreciated! many thanks!
I have a little problem with a filtered report.
OK I have a table with 3 fields,
SERIE is a text field related to another table's primary key
NUMBERis a numeric field and
NUMSP is a text field and the primary key.
I have a data entry form on which a user inputs the SERIE value with a dropdown list. Immediately, the BeforeInsert event of the form sets the NUMBER value to Dmax("NUMBER","TABLE", , ) +1 or something to that effect, and the BeforeUpdate event set the vale of NUMSP to "SP"&[SERIE]&[NUMBER]&Year(Date()) or something to that effect with a macro
OK for now everything on the form works fine.
I have a report which I want to preview and filter so it shows only the current record on the form. This is done by filtering the DoCmd.OpenReport so that the report's NUMBER equals the form's NUMBER . And dit works.
Now, I need to set a criteria to the DMax function, so the DMax searches the highest value where SERIE equals the SERIE value on the form. This is to have the NUMBER incremented by 1 and maintain the sequence for each SERIE value, This of course will generate repeated values of NUMBER, so I cannot use that field in the report filtering. So I want to change the report filtering to the NUMSP field. I will do this when I figure out how to solve the problem, which is...
that if I change the report filtering to [NUMSP] = Me.NUMSP, the report will prompt me for values of "SP" and [SERIE]
I do not know what the problem is since there are no repeated values of NUMSP.
I have tried to explain things as well as I can, have searched the web and the forum for a solution but I haven't found anything similar enough to this issue that will help me.
Any help will be greatly appreciated! many thanks!
