Form/Sub Form Help (1 Viewer)

[andywhittle]

Hi I'm really stuck please help.

I have a Table (Invoices) including field (Invoice Number) a form (Main Menu Search) with a txt box search on it (txtInvoice_Number)

On the form I have a subform (subfrmInvoices)

What I want to do is:

* User types in text box what Invoice Number is
* Clicks Search button
* The results if any are brought up in the subform (subfrmInvoices)

I haven't a clue how the code should be like, I am new to this kinda stuff!

Thanks for any help in advance!!

Andy

 

[gemma-the-husky]

I would store a global variable, and function to read it

dim gblInvNumber as long

function readInvNumber() as long
readinvnumber = nz(gblinvnumber,0)
end function

then have a query based on your invoices with the criteria =readinvnumber()
This will find at most one invoice (assuming invnumber has no duplicates.

Hence when yuor user clicks search, your code does

gblinvnumber = mysearchno

and now if dcount("*","myquery")=0 you have no search hits. if its one you can display the form.

You dont need a search button, you can actually put this in an after update event on the search text box, which saves your user a click.

Depending on how many invoices you have, you could even populate a combo box with all the possible invoices, together with other details like date, value etc. You dont want thousands of rows in a combo box though. As combo boxes are self seeking, it will jump to the correct invoice as users begin to type numbers in.

You can do all this through a single form, rather than using a subform - depends on how you want to present it. There is a useful command application.runcommand accmdsizetofitform (i think thats the name). If you put your selection criteria in the form header, and the results in the form detail, the accmdsizetofit actually shrinks the form to show only the header if there are no results - it can look impressive.

 

[andywhittle]

Thanks for the reply!!

Thanks for the reply!!

I really am a novice at this stuff, so sorry to be a pain with my questions. Can I see if i've got this right?

So I have my txt box called InvNumber

Where do I put the global variable code, can i insert it anywhere in the rest of the code?

And I put this:

gblinvnumber = mysearchno

if dcount("*","myquery")=0

in the After click Update event of the search box?

Thanks

 

[gemma-the-husky]

you define the global variable in a module, with the line

public gblInvNumber as long

this makes it persist throughout the application. You can use this same variable (or other variables) for other forms also, or define other variables If you re-use it, you need to be aware of that the value may have changed

in the module you also need a function, to be able to use the value in a query, as you cant directly read a variable in a query.

public function readGblInvNumber() as long
readGblInvNumber = nz(GblInvNumber,0)
end function

now you need to deifne a query (or you can do it with a sql statement). include the invoice table, and drag the invoice number into the design area. If you open the query, you will see every invoice number. If you now in the criteria section put

=readgblinvnumber() - the brackets have to be there

it will now only return any matches for the specific setting of the gblinvnumber - in this case the function returns 0, so you shouldnt get any.

NOW in your main form, after you enter the search number, when you click the search button, (you can refine it to do different things later), you need the buttons click event to include the following

gblinvnumber = nz(searchnumber ,0)

this sets the gblinvnumber to equal the value of the required number. the nz expression is defensive, as it deals with a problem that would happen if someone clicked the butoon while the searchnumber was blank.

now if you open the query we designed before you should see 1 record, if the searchnumber matches an invoicenumber and 0 records if not.

you can refer to this query in the code in a number of ways, but the dcount merely counts the items
hence we get

gblinvnumber = nz(searchnumber ,0)
if dcount("*","my query name") = 0 then

 

[gemma-the-husky]

you define the global variable in a module, with the line

public gblInvNumber as long

this makes it persist throughout the application. You can use this same variable (or other variables) for other forms also, or define other variables If you re-use it, you need to be aware of that the value may have changed

in the module you also need a function, to be able to use the value in a query, as you cant directly read a variable in a query.

public function readGblInvNumber() as long
readGblInvNumber = nz(GblInvNumber,0)
end function

now you need to deifne a query (or you can do it with a sql statement). include the invoice table, and drag the invoice number into the design area. If you open the query, you will see every invoice number. If you now in the criteria section put

=readgblinvnumber() - the brackets have to be there

it will now only return any matches for the specific setting of the gblinvnumber - in this case the function returns 0, so you shouldnt get any.

NOW in your main form, after you enter the search number, when you click the search button, (you can refine it to do different things later), you need the buttons click event to include the following

gblinvnumber = nz(searchnumber ,0)

this sets the gblinvnumber to equal the value of the required number. the nz expression is defensive, as it deals with a problem that would happen if someone clicked the butoon while the searchnumber was blank.

now if you open the query we designed before you should see 1 record, if the searchnumber matches an invoicenumber and 0 records if not.

you can refer to this query in the code in a number of ways, but the dcount merely counts the items
hence we get, within the click event

gblinvnumber = nz(searchnumber ,0)
if dcount("*","my query name") = 0 then
msgbox("no matches found")
----- any other code -----
else
msgbox("matches found")
------ any other code -----
end if

Now you know (whether) what you want to do with your subform etc, However, If all this is new to you, you will really struggle to get very far designing a usable database, as this is fairly basic stuff. Pretty well the whole of access is based on managing events and coding actions based on those events.

 

[gemma-the-husky]

sorry managed to post an unfinished version of my reply - i keep hitting tab in the query pane, which takes me to the post button!

 

[dkirk02]

A way to do this without complicted VB code would be the following
1.change your sub form to another regular form and have it stand on it's own rather than being a sub form embedded in the main form
2.build a query on whatever table holds the records to be populated in the form once the invoice number is searched
3.set the criteria in your query in the invoice number field to [Forms]![Main Menu Search]![txtInvoice_Number] (watch out for naming conventions here, you may have to modify the name of your form to read Main_Menu_Search)
4.go into design view of your search results form and set the record source of the overall form to the query that you just built
5.go into design view of your "Search" button, and on the On Click property, place "docmd.openform "search results form name here"

hope that works for you!

Cheers,
Deeta in KC