Search Field in Form pulling info from 2 tables

[Gambit17]

Hi is it possible to have criteria in a form that will display info from 2 tables, however the info might be different. I can do it for one table but when I add another table to the form, the SQL adds INNER JOIN which seems to link the 2 tables which is not what I want. What criteria could I use to fix this?

At the moment I am using

Like [forms]![Search].[myselector] & "*"

as the criteria for both fields.

Ideally I would like the form to display data from the one table and data from the other table. If the criteria doesnt match the one table then those fields will be blank ,but in the tables fields will still show the relevant data.\

Thank you so much

[offie]

Could you provide an example of the recods in the 2 tables and then what you would like to get?
Why not make query with the 2 tables and then search through that. If you want only the records that have a certain field in common the default inner join is what you want, if you want to show ALL the records, giving null values when the information does not add up, you can use left join.

Why exactly do you not want them linked?
Do you want to display 2 queries in one form?

[Gambit17]

Hi Offie thank you for your reply. This might help.

Hi I have a form that has a text box in the header and when the user enters the farmer Code into the text box the relevant record is displayed.
I am using the following criteria in the query within my Form Cotton12:
Like [Forms]![Cotton12].[2012]
Now I have 2 tables Table Cotton12 and Table Cotton11. They contain a database of farmers, from the year 2012 and 2011. Sometimes the data is the same sometimes it is not. For example Farmers are given farmer codes, however due to incorrect capturing of data different farmers have been given different codes throught over the years.
What I would like to do is in the same form, have the user enter in a farmer code and data from the 2012 table come up and the data from 2011 table come up so they can be compared and if that code is not in one of the tables the fields relating to that table stay blank.
For E.g. the form might look like this.
2012 Farmer Name Acreage Yield Estimate 2011 Farmer Name Acreage Yield Estimate

You see often for the same Farmer Code there may be a different Farmer name etc.
Your help would be much appreciated thank you

[offie]

First off, what is the data is two different tables? Why not Append them an add a year field, this way when you search a farmer code you get all the information in one go.

If if you really want the 2 tables...
What is the name of the text box? 2012?
Also it should be [Forms]![FormName]![TexboxName] not with a period

you can add the same criteria to 2 different queries no problem

Then make 2 subforms, of you aren't looking for anything fancy just drag the queries that have the criteria in them into the form

When you make your command button, on-click, you'll have to make it a procedure, and then edit the VBA to something along the lines of:
DoCmd.OpenQuery "COSumAll"
DoCmd.Close acQuery, "COAll"
[Forms]![FormName].Requery
[Forms]![FormName].[Subform].Requery (with subform being the name that you assign to the queries, usually the same)

You could probably do this with macro's but I'm not sure how

That should do it I think

[Gambit17]

OK Thanks Offie,

Yeah the name of the text bo is 2012.

I am a novice at Access so I just need to make sure I understand correctly.

I am making the query wrongly or rightly in the record source of the form. Should I be doing that?

How do you add a subform?

Often the data will be different in the two different table I just want the user to be able to compare them to see which is correct for example.

After imputing Farmer Code 1.1.5. the form might pull up this info.

2012
Farmer Name: Peter
Acre: 0.4

2011
Farmer Name: John
Acre: 0.5

The reason I need the user to compare the 2 sets of data is that some of them are incorrect and some are correct.

[Gambit17]

It works you are an absolute genious i haven't entered the command button what exactly is that?

thank you

[offie]

Well if it works the way you want it to then you don't need it.

What a command button does is to run a function or code or marcro
So in this case, when you enter the farmer code and want to submit the data
>you open and close the 2 queries so that they refresh with the new data (assuming they were close to begin with)
>Then Refresh the form, and the subform

So that all in one button you have all the data you want

[Gambit17]

Ok Cool thanks. It is working, but it doesnt seem to work if I enter a farmer code that isnt in one of the tables. For example the farmer code is in 2011 bu not in 2012 then nothing is displayed why is this?

[offie]

I dont know, do you have a relationship between the two? can you add a picture of what the form looksl like? You can black out the data. And the SQL for the 2 queries