New Data in tables now showing in query

[WesHarding]

Hi everyone,



I'm brand new to this website, and usually am one to simply read and follow threads to solve my Access issues. However, I need your help.

I have a database in which I have set up forms to input data into my tables. This function successfully inputs data into my original tables. My problem is, this newly input data is not showing in my queries that relate to my search forms. And in turn, the newly input data cannot be searched by users.

I hope this makes sense to some degree. I've heard this may be the result of inner join or out join issue, but I don't fully understand this concept. Any help at all would be greatly appreciated.

Thank you

Here is the SQL for my main search query:

SELECT tblFarmerInfo.FirstName, tblFarmerInfo.LastName, tblFarmerInfo.email, tblFarmerInfo.website, tblPhone.[Phone Number], tblFarmerInfo.facebook, tblAddress1.Address1ID, tblAddress2.Address2ID, tblFarmerInfo.DateofEntry, tblFarmName.RegisteredFarm, tblFarmName.RegisteredBusiness, tblFarmName.FarmName, tblFarmName.AreaofSale, tblFarmName.Export, tblAnimal1.AnimalName, tblAnimal2.AnimalName2, tblAnimal3.AnimalName3, tbl1Crop.CropName1, tbl2Crop.CropName2, tbl3Crop.CropName3, tblFarmName.FarmName, tblFarmName.RegisteredFarm, tblFarmName.RegisteredBusiness, tblFarmName.AreaofSale, tblFarmName.Export
FROM tblFarmName INNER JOIN ((tblAddress2 INNER JOIN (tblAddress1 INNER JOIN (((tblPhone INNER JOIN ((tblAnimal3 INNER JOIN (tblAnimal2 INNER JOIN (tblAnimal1 INNER JOIN ((((tbl3Crop INNER JOIN (tbl2Crop INNER JOIN (tbl1Crop INNER JOIN (((tblFarmerInfo INNER JOIN [tblCrop3-Farmer_Link] ON tblFarmerInfo.IDfarmer = [tblCrop3-Farmer_Link].FarmerID) INNER JOIN [tblcrop2-farmer_Link] ON tblFarmerInfo.IDfarmer = [tblcrop2-farmer_Link].FarmerID) INNER JOIN [tblcrop1-Farmer_Link] ON tblFarmerInfo.IDfarmer = [tblcrop1-Farmer_Link].FarmerID) ON tbl1Crop.[1CropID] = [tblcrop1-Farmer_Link].[1CropID]) ON tbl2Crop.[2CropID] = [tblcrop2-farmer_Link].Crop2ID) ON tbl3Crop.[3CropId] = [tblCrop3-Farmer_Link].Crop3ID) INNER JOIN [tblAnimal1-Farmer_Link] ON tblFarmerInfo.IDfarmer = [tblAnimal1-Farmer_Link].FarmerID) INNER JOIN [tblAnimal2-Farmer_Link] ON tblFarmerInfo.IDfarmer = [tblAnimal2-Farmer_Link].FarmerID) INNER JOIN [tblAnimal3-Farmer_Link] ON tblFarmerInfo.IDfarmer = [tblAnimal3-Farmer_Link].FarmerID) ON tblAnimal1.AnimalID = [tblAnimal1-Farmer_Link].Animal1ID) ON tblAnimal2.[Animal2ID] = [tblAnimal2-Farmer_Link].Animal2ID) ON tblAnimal3.[Animal3ID] = [tblAnimal3-Farmer_Link].[3AnimalID]) INNER JOIN [tblphone-farmer_Link] ON tblFarmerInfo.IDfarmer = [tblphone-farmer_Link].FarmerID) ON tblPhone.PhoneID = [tblphone-farmer_Link].PhoneID) INNER JOIN [tblAddress1-Farmer_Link] ON tblFarmerInfo.IDfarmer = [tblAddress1-Farmer_Link].FarmerID) INNER JOIN [tblAddress2-Farmer_Link] ON tblFarmerInfo.IDfarmer = [tblAddress2-Farmer_Link].FarmerID) ON tblAddress1.ID = [tblAddress1-Farmer_Link].Address1ID) ON tblAddress2.ID = [tblAddress2-Farmer_Link].Address2ID) INNER JOIN [tblFarm-Farmer_Link] ON tblFarmerInfo.IDfarmer = [tblFarm-Farmer_Link].FarmerID) ON tblFarmName.IDfarm = [tblFarm-Farmer_Link].FarmID
WHERE (((tblFarmerInfo.FirstName) Is Null Or (tblFarmerInfo.FirstName) Like "*" & [Forms]![MainSearchForm]![txtfirst] & "*") AND ((tblFarmerInfo.LastName) Is Null Or (tblFarmerInfo.LastName) Like "*" & [Forms]![MainSearchForm]![txtlast] & "*") AND ((tblFarmerInfo.email) Is Null Or (tblFarmerInfo.email) Like "*" & [Forms]![MainSearchForm]![txtemail] & "*") AND ((tblFarmerInfo.website) Is Null Or (tblFarmerInfo.website) Like "*" & [Forms]![MainSearchForm]![txtwebsite] & "*") AND ((tblPhone.[Phone Number]) Is Null Or (tblPhone.[Phone Number]) Like "*" & [Forms]![MainSearchForm]![txtphone] & "*") AND ((tblFarmerInfo.facebook) Is Null Or (tblFarmerInfo.facebook) Like "*" & [Forms]![MainSearchForm]![txtfacebook] & "*") AND ((tblAddress1.Address1ID) Is Null Or (tblAddress1.Address1ID) Like "*" & [Forms]![MainSearchForm]![txtaddress] & "*") AND ((tblAddress2.Address2ID) Is Null Or (tblAddress2.Address2ID) Like "*" & [Forms]![MainSearchForm]![txtaddress] & "*") AND ((tblFarmerInfo.DateofEntry) Is Null Or (tblFarmerInfo.DateofEntry) Like "*" & [Forms]![MainSearchForm]![txt_search_dateofentry] & "*") AND ((tblFarmName.RegisteredFarm) Is Null Or (tblFarmName.RegisteredFarm) Like "*" & [Forms]![MainSearchForm]![cbregisteredfarm] & "*") AND ((tblFarmName.RegisteredBusiness) Is Null Or (tblFarmName.RegisteredBusiness) Like "*" & [Forms]![MainSearchForm]![cbregisteredbusiness] & "*") AND ((tblFarmName.FarmName) Is Null Or (tblFarmName.FarmName) Like "*" & [Forms]![MainSearchForm]![txtfarmname] & "*") AND ((tblFarmName.AreaofSale) Is Null Or (tblFarmName.AreaofSale) Like "*" & [Forms]![MainSearchForm]![cmbareaofsale] & "*") AND ((tblFarmName.Export) Is Null Or (tblFarmName.Export) Like "*" & [Forms]![MainSearchForm]![cbexport] & "*") AND ((tblAnimal1.AnimalName) Is Null Or (tblAnimal1.AnimalName) Like "*" & [Forms]![MainSearchForm]![txtanimalname] & "*") AND ((tblAnimal2.AnimalName2) Is Null Or (tblAnimal2.AnimalName2) Like "*" & [Forms]![MainSearchForm]![txtanimalname] & "*") AND ((tblAnimal3.AnimalName3) Is Null Or (tblAnimal3.AnimalName3) Like "*" & [Forms]![MainSearchForm]![txtanimalname] & "*") AND ((tbl1Crop.CropName1) Is Null Or (tbl1Crop.CropName1) Like "*" & [Forms]![MainSearchForm]![txtcropname] & "*") AND ((tbl2Crop.CropName2) Is Null Or (tbl2Crop.CropName2) Like "*" & [Forms]![MainSearchForm]![txtcropname] & "*") AND ((tbl3Crop.CropName3) Is Null Or (tbl3Crop.CropName3) Like "*" & [Forms]![MainSearchForm]![txtcropname] & "*") AND ((tblFarmName.FarmName) Is Null Or (tblFarmName.FarmName) Like "*" & [Forms]![MainSearchForm]![txtfarmname] & "*") AND ((tblFarmName.RegisteredFarm) Is Null Or (tblFarmName.RegisteredFarm) Like "*" & [Forms]![MainSearchForm]![cbregisteredfarm] & "*") AND ((tblFarmName.RegisteredBusiness) Is Null Or (tblFarmName.RegisteredBusiness) Like "*" & [Forms]![MainSearchForm]![cbregisteredbusiness] & "*") AND ((tblFarmName.AreaofSale) Is Null Or (tblFarmName.AreaofSale) Like [Forms]![MainSearchForm]![cmbareaofsale] & "*") AND ((tblFarmName.Export) Is Null Or (tblFarmName.Export) Like "*" & [Forms]![MainSearchForm]![cbexport] & "*"))
ORDER BY tblFarmerInfo.FirstName;

[ranman256]

If the data is in the table, it will show in a query, unless you have criteria that excludes it.
If you are entering data in a parent\child subForm, it's possible you did not link the subForm to the parent and you are loosing the keys.
If you need related keys, then the subForm properties:
Link parent field=keyField
link child field=keyField

[WesHarding]

Hi,

Thanks for the reply! I think there must be some sort of relationship problem between my tables. I just made a new query, and all the data in my tables appears.

However, when I input new data into the tables, I will have to make a new query to make it appear in the query (and thus in my forms)

[NTC]

to add to R256's input: you write - "newly input data is not showing in my queries that relate to my search forms. And in turn, the newly input data cannot be searched by users."

One can not really parse your SQL syntax out of context; so either your query has a parameter that excludes your new info - or - when putting in info into the form for some reason a primary key field is not being entered into the sub form so that the records can't join....

[WesHarding]

Hi NTC,

I removed all conditions from the search query and ran it to see if it would include the missing information. However it is still not showing my recently added data. Is there another way to remove any other parameters perhaps? I'm quite new to Access, so this is a bit of a learning curve for me I apologize.

[NTC]

Well in a multi table query the join between tables is a criteria (in a sense) and that may be the reason. Look directly in the table where the data should be to see if it is there.

If it is there - look to see if there is data in the field on which you are making a join.

If it is not there then the actual write event is not occurring when in your form for some reason.

[WesHarding]

Hi! So I think I figured out my issue!

I had to do stacked query (which I hadn't ever done before). This allowed me to do the out joins I was wanting to achieve, but didn't know how to describe!

Thank you very much for helping me in the right direction!