Checking if a value in a record is in a particular format

[grantwilliams]

Hi,

I need to generate a report for matters recorded in a database which don't have a correct file number recorded. I want to find all records for a particular year where the file number is recorded incorrectly. The file numbers are stored in the format XXXX/xxxxxx where XXXX is the year, and xxxxxx is the file number for that year e.g. 2003/000001

I also have another field (IDCode) which is a number corresponding to the year e.g. 3 for 2003, 2 for 2002 etc.

Is there a way of using VBA to find all records for the year which are not in the correct format, have the default value of 'XXXX/' OR have a value of 'XXXX/000000' (which is our default for matters not on a paper file)???

The column name is FileNo and the table is tRegister.

I have an input mask on the form used to enter the file numbers, but as the database holds some older data, I am unable to put the mask over the column in the table, just the new data entered.

Has anybody got any ideas or know-how on this? I'm only a newbie to VBA so any and all help is appreciated.

Grant

 

[TonyJollans]

Hi grantwilliams,

An Input mask controls what you can input; it does nothing in terms of what you select from your table. The SQL you require for that is something along the lines of ..

SELECT tRegister.IDCode, tRegister.FileNo
FROM tRegister
WHERE tRegister.IDCode=3 AND (tRegister.FileNo Not Like "????/??????" OR tRegister.FileNo Like "????/000000"

Do you particularly want to do this in VBA? If you set up a Query you can easily generate a report based on it without code.

Enjoy,
Tony

 

[grantwilliams]

Thanks! Worked a treat... don't know why I was looking at VB for it in the first place!!!

The next problem I have is that I now have the results I require loaded into a text box called List on a form called FileReport. What I now need to do is have a Print button on the FileReport form which reads each record in the text box (List) and opens each record in another form called Register. The register form is then printed and closed and the sequence moves onto the next record.

To this point, I can double click each record to open it up in the Register form, but as some searches respond with 5500 results, opening each individually is simply not an option.

Does anyone know of a way of doing this? I have the following code so far:

Private Sub PrintForm_Click()
On Error GoTo Err_PrintForm_Click

    Dim frm As Form, ctl As Control, varItem As Variant
    
    Set frm = Forms!FileReport
    Set ctl = frm!List
    
    For Each ctl In Controls
        DoCmd.OpenForm "Register", , , , , , "FileReport"
        DoCmd.PrintOut
        DoCmd.Close "Register"
    Next ctl

Exit_PrintForm_Click:
    Exit Sub

Err_PrintForm_Click:
    MsgBox Err.Description
    Resume Exit_PrintForm_Click

End Sub

This has mixed results. It opens the form and attempts to print, but does not pass any data to the form and I get a Type Mismatch error. The form does not close.

Register has the following code in it for recognising which record needs to be displayed.

    ElseIf Me.OpenArgs = "FileReport" Then
        Me.RecordSource = "qfRegisterFileReportSource"

qfRegisterFileReportSource reads the List from the first form to see which record is required.

I'm guessing that the problem is my code for reading down through the records... as I've never done this before, what I have done so far has only been me playing around with it and any part that's on the right track is nothing more than coincidence!

I appreciate any and all assistance given on this!

Grant

 

[TonyJollans]

Hi Grant,

I don't completely follow what you're trying to do - but you do say you're playing around - keep playing, it's a good way to learn.

It all seems a bit complex. You have the results of your query. You say they are in a textbox. Do you mean that you have a Form based on the query and that each row is one record on the form, or what?

Your code loops through each Control on the Form (i.e. the textbox, the button, whatever else you have) - it doesn't make a whole lot of sense to be opening another form for each of these - and I don't understand how you're trying to get the data for your print (but you do say it's not working).

What I guess you're trying to do is to open a form for each record in your query result set, but is there any reason to process (and print) each record independently? Have you tried creating a Report based on your Query (instead of a Form)? You can set each record to print on a new page if that's what you want.

Can you post a bit more about all that you actually want.

Enjoy,
Tony