I have two identical forms/tables, one is for 2000 and the other 2001. I am not savvy with macros or codes. I have a field in both forms called stock#. I would like when you search frm2001 for a stock# and it does not appear in that form to open frm2000 and search there and then allow any additions, editing needed (in either form it is found in).

I assume a macro could handle this function but I have not been able to make one work. Could someone tell me how to do this and explain it to me as if you were talking to a 4-year old? :-)

Thank you!

I am assuming you are search the table for the stock part. and then opening the relevant form if it is found.

Add the following code whereever you are doing your search:

' Assume intStockNum stores the Stock# you are looking for.
' Assume both forms and tables are named similarly, frm2001 and frm2000

If StockExists(intStockNum, "frm2001") then
docmd.openform "frm2001", , ,"[Stock#] = " & intStockNum
Else
if StockExists(intStockNum, "frm2000") then
docmd.openform "frm2000", , , "[Stock#] = " & intStockNum
Else
MsgBox "Stock " & Str(intStockNum) & " does not exist"
End if
End if

This code snipet call the helper function StockExists taht you will have to define in the module itself. Just type the following:

Private Function StockExists(intStock as Integer, strtblName as String) as Boolean
StockExists = iif(IsNull(Dlookup("[Stock#]", strtblName, "[Stock#] = " & intStock),False, True)
End Function

Explanation:

I am assuming you will be getting a stock number value from the user at some point. Possible a form. The user must perform some action to trigger thesearch. Usually a command button. The above code could go into the OnClick event for the command button.

The first if..then statement calls the StockExists function with two arguments, the stock number and the table to search. The function simply looks for the value and if it is found returns true otherwise it returns false.

So assume you pass a stock number that exists in your table frm2001. The if statement will be true therefore the next line will be executed. The docmd.openform statement will open the form that you want. In this case the form frm2001. the piece in quotes tells the form to open showing only records that satisfy the condition "the [Stock#] value from your table is equal to the intStockNum value from your user". I assume only one record will satisfy this condition from your table, but that depends on your table design.

I hope this explanation surfeits and that my understanding of your question was correct.

[This message has been edited by ntp (edited 01-09-2001).]

Thank you so much, I really appreciate your help. However, you lost me in a couple of areas. :-)

1. I am not sure what you mean by intStockNum.

2. Where do I insert this code:

Private Function StockExists(intStock As Integer, strtblName As String) As Boolean
StockExists = iif(IsNull(Dlookup("[Stock#]", strtblName, "[Stock#] = " & intStock),False, True)
End Function

I inserted the first part of the code:

If StockExists(intStockNum, "frm2001") then
docmd.openform "frm2001", , ,"[Stock#] = " & intStockNum
Else
if StockExists(intStockNum, "frm2000") then
docmd.openform "frm2000", , , "[Stock#] = " & intStockNum
Else
MsgBox "Stock " & Str(intStockNum) & " does not exist"
End if
End if

On the "onclick" event of the command button (event procedure).

Am I anywhere close to getting this? I just hate it when software is smarter than me! :-)

Why do you need seperate tables for what I assume is different years?

I took a beginner class in Access, everything else I have learned is self-taught. One would think structure would have been a topic covered in the class. But as I learned a lot in the past year, I also learned my structure was very poor. I am setting up a new database but in the interim I am using two tables.

1:
The variable intStockNum stores the value you are looking for. i assume it may be from some textbox that the user is entering the stock code to be searched for.
For example you might have a textbox called 'txtStockCode' into which the user types in a stock code then clicks on the command button to initiate the search.
Instead of using intStockNum as I did, you would replace tha twith something like 'me.txtStockCode.value'

2:
You can type in the code for the function StockExists in module for the form that has the code for the command button. The same module with your OnClick event procedure.

Once you've done these things everything should work fine but then again.

And finally I agree with richie the two table are redundant. All you need is a year field in the table to differentiate betwwen 2001 and 2000. i'm assuming that is the only difference between them.

Good Luck.
ntp

Just wanted to say thank you for all of your help. Now I know how to synchronize the forms but the more I think about it, I have to agree it makes more sense to use one table.

Thanks again!