Multi-table queries not displaying any data

[orange]

Good stuff Mike.
Michael seems keen to learn and build this database. I think we should keep him pointed to "best pratices" whatever they may be. My intent is to help and avoid confusion and I think these steps help do that.
Glad you are helping/advising.

[mike60smart]

I learn every day as well. So glad to help.

[Michael91]

Hi Michael;

Glad to hear you are progressing and getting some help.

When you enter data, or even when you want to check that entered records are available for confirmation activity it is wise to build a quick form or a query or whatever and check,review and confirm before entering ALL of the data. You'd be surprised at the number of people who have spent weeks/months entering their data only to find out that some field was missing, the relationships are incorrect or the design doesn't allow for XXX.

Work against the model; test and confirm; adjust if necessary; keep notes on issues as they arise (a log of things such as broken buttons;...anything that is problematic no matter how trivial ---you can always ask the forum, and when you do you'll have some context/detail that will facilitate communications.

I personally would stay clear of macros. There will be 1 or more vba based solutions in most cases. The forum can help with that.

I see that Mike has responded with fixed issues so haven't pursued any detail. I suggest that when you provide a database, or when someone sends a database in response, that it is clearly identified so you/we don't work on an already solved, or worse a new version headed down a new path. You can use version numbers/subnumbers whatever you need. Since it's your project, I suggest you assign versions to your databases as you change things.

I don't know if your Database_v_12.zip is the same as the attached to Mike's post or not. I'd think not since he said he fixed things.


When you do have an issue===identify the form or table, any event info or specific line. If you have an issue where you tried X and got an error or B, tell us what you did, the data you used and describe what you expected to get.
eg
1) I clicked the right arrow button on FormGetInfo I thought I'd go to the first record; but I got error 999.
2) I ant to divide the TotalAmount by the number of months, but I got an error that said Error202 division by 0.

When you say you are populating Listboxes, how exactly are you doing that---point form please. There may/may not be an opportunity to try another method.

Getting things done is one thing, but there may be some optimization and learning options that will give you a degree of self-confidence with the database and processes. The forum is available to advise and answer questions - especially those where you have put in effort. We do get the requests "Could somebody create an accounting system for me?" You know your archaeology subject matter, you have a good idea for what you want, it's now a question of How to do this with Access--and that sets you in position to get focused responses.
Just remember, we know some things about databases and Access, but zip of archaeology. So communications - terminology, examples, analogies, concepts will be key and clarity is expected.

Enough soap box. You can PM me, or ask in the forum as you see appropriate. There are many ways to accomplish things and no one person has them all.

Good luck.

Hi Orange,

Sorry if I have annoyed you, or gone about this the wrong way, I was trying to provide an update. Apologies also for being unclear about terminologies again. I should say again Mike has been a MASSIVE help the last few days, he has solved a lot of issues and taught me a lot between my last post and now.

I appreciate my issue description was a bit vague so for clarity:

1) In frmDeposits, Subform frmPartialSecondary –entering data for record DepositID 7 – When selecting any of the preset values for the field PartialElementsList (labelled as elements list), and pressing the associated “add elements” button, I get the following error:
Error 2450 (Microsoft Access cannot find the referenced form ‘frmPartialSecondary’.) in procedure btnAddElements_Click, line 30.
What I want to do, is be able to select one or several preset values here, press the button, and they should populate the table tblPartialElementsLink. Each value in the field PartialElements (listbox) represents a part of the body (hands, feet, head, torso), and as this subform relates to partial or incomplete burials (deposits), I want to be able to select which parts of the skeleton are present. What should also happen, is the selected values should appear in subform frmPartialElementsLinkSubform, and if the values selected in the field PartialElements also change, and the associated button is pressed again, then the values in frmPartialElementsLinkSubform should change also.

2)In frmDeposits, subform frmOsteoInfo –entering data for any Deposit – When selecting any of the preset values for the field OsteoAnalysisList (labelled as Osteo Detail), and pressing the associated “Add osteological analysis” button, I get the following error:
Error 2450 (Microsoft Access cannot find the referenced form ‘frmOsteoInfo’.) in procedure AddOsteoAnalysis_Click, line 30.
As above, what I would like to do is select one or several preset values, press the button, and the values should populate the table tblOsteoLink. Again there is a subform frmOsteoLinkSubform that should display these values.

3) In frmDeposits, subform frmScienceInfo – entering data for any deposit - When selecting any of the preset values for the field ScienceAnalysisList (labelled Analysis), and pressing the associated “Add scientific analysis” button, I get the following error:
Error 2450 (Microsoft Access cannot find the referenced form ‘frmScienceInfo’.) in procedure btnAddScienceAnalysis_Click, line 30.
Again, this serves the same purpose as the above two procedures and should work exactly the same, but this is for recording different types of scientific analysis that may have been undertaken for each deposit – e.g. DNA testing. Again there is a subform frmScienceAnalysisSubform that should display the entered values.

I have checked through the VBA and can’t see any obvious errors, and Mike looked for me as well (thanks again Mike) and he couldn’t work it out either. He came up with the query/macro solution (which does work great!) but if someone could help me better understand where I am going wrong in the first place it would be a great help. If that is not detailed or clear enough do say and I will reword/expand on it.

I see that Mike has responded with fixed issues so haven't pursued any detail. I suggest that when you provide a database, or when someone sends a database in response, that it is clearly identified so you/we don't work on an already solved, or worse a new version headed down a new path. You can use version numbers/subnumbers whatever you need. Since it's your project, I suggest you assign versions to your databases as you change things.

I have implemented a number system as you suggested previously – my version 12 is/was the most up-to-date version until Mike’s reply, and the relevant one for solving the current issues If/when I upload it next it will have a new number.

When you enter data, or even when you want to check that entered records are available for confirmation activity it is wise to build a quick form or a query or whatever and check,review and confirm before entering ALL of the data. You'd be surprised at the number of people who have spent weeks/months entering their data only to find out that some field was missing, the relationships are incorrect or the design doesn't allow for XXX.
I do appreciate this – I am not ready for full data entry yet, there are still only a few test records in the database, that is how I spotted/tested the errors. I want to sort this before I build some test queries, and then I will see if I can start entering more data

I hope that is clearer and more focused?

[mike60smart]

Hi Michael

In my latest version 12.4 Mike Version that I uploaded I have fixed the PartialSecondary and the OsteoInfo problems you were having.

If you study these fixes and see if you are able to resolve the last error on the Scientific Analysis.

[orange]

Hi Michael,
I think you may have misinterpreted my post or that my words and intention wasn't clear. I am not annoyed in any way. I was trying to advise that, when posting an issue - provide details and examples if possible. That will get you more focused responses. I realize that Mike is assisting and advising you and that's great. Mike is helpful on many forums.

You are going about things in the proper way in my view. You have worked on a model. You have asked good questions on the forum and have responded positively in all interactions. An analyze, design, test, adjust cycle to get things working before committing full in- perfect.

I'm not sure how you are keeping track of issues, but am suggesting a log of some sort. I used a small table in a copy of your database v 11 when I was getting names and descriptions documented. It wasn't much but seemed to work for me. The key point is to write down issues for reference.

I haven't looked at the 3 issues you posted in last post. These are clear and will take some investigation. I'll look at them and get back. I am working on something else, but will look and respond.

It appears that in Mike's (#124) he has resolved 1 and 2. And has afforded you an option to "undertake" 3.
If you have specific VBA questions don't hesitate to ask. Often, if you see a sample, you can work out the logic and the vba syntax. There are several sample available via Google, youtube.. I have some links in my signature that reference tutorials and articles.

You are up to your ears with your project and have had a lot of database thrown at you in a short time. You're doing just fine. Keep at it.

This is a sample of the Comments table I used with Database v 11 previously. I had a table, query, form and report all referencing the Table. I attached parts of the report in previous posts.

Table layout:
Attachment 47923


Form design:
Attachment 47922

The specifics of the table may not be adequate for a log for your project, issues, solutions, status.... But is intended to show the kinds of things that could be recorded. Just saying it doesn't have to be a professional project management system. Just an accessible repository to record "things of interest (or frustration)" and to keep some relevant facts for reference.

That's it for now.

[orange]

I took a quick look at you Database v12. And at Mike's version regarding the
1) In frmDeposits, Subform frmPartialSecondary. It appears that Mike's version works.
I tried it by
-setting up a query with all three tables partialSecondaryInfo, PartialLink and PartialList
-then set criteria to Deposit 7. Saw what existed (3 elements)
-then put a breakpoint on the DCount line 20

20    If DCount("PartialSecondaryID_FK", "tblPartialElementsLink", "PartialSecondaryID_FK=" & Me.PartialSecondaryID) > 0 Then
30    ClearPartialElements 'clear all existing site types for this siteID
40    End If

-put a breakpoint on line 30
-went back to the form clicked on add element button
-after processing line 20 and before processing line 30
-copied this part of line 20
DCount("PartialSecondaryID_FK", "tblPartialElementsLink", "PartialSecondaryID_FK=" & Me.PartialSecondaryID)

-put a question mark in immediate window and then pasted the blue line then hit enter
(this processes the command line and returns the count--it was 3 just as expected.)

The code worked; all seems well. I think the Error 2450 was in the setup of the Dcount. See Mike's version for proper set up.

Next, 2)In frmDeposits, subform frmOsteoInfo

I went to Mike's code and could not get it to work. The code set up appears identical to the code used in Partial stuff. I had to go to Deposit 5 to actually get a Osteo record with data. I again set up a query and could see 1 OsteoAnalysis (calculus), but I could not get the button to add a record. I think the subform to confirm things may not be working?? as he expected, but it may be that I missed something key to the issue?

I am in the middle of something else, so perhaps didn't try hard enough??? If you or Mike can confirm it works or doesn't work, we'll be in better position for what to do next.

I did not look at item 3) In frmDeposits, subform frmScienceInfo – entering data for any deposit

Bottom line: All issues seem related to Error 2450 -so that is consistent. Access is reporting a issue locating the form involved.
So review Mike's solution to 1)
; try his Osteo fix and confirm it works
; if it does go to solving 3) ---if it doesn't work, then
;review his solution to 1).

He has resolved the 2450 error in 1) for sure. So studying that will help with any/all 2450 errors.

Update: About 45 minutes after posting, just tried Mike's v12 Osteo again. I tried working with Deposit 4
it has 1 OsteoAnalysis Calculus, but then gave me Error 2450 on FormOsteoInfo

[orange]

Update again:

Using Mike's V12, Osteo deposit 4 OsteoInfo 5, attempt to add 3 OsteoAnalysis as per attached. When I stepped through the code it was erroring in the ClearOsteoAnalysis.
So, I added an error routine in that sub and got the direct error as attached. Seems to be same error Michael has reported, but seems fixed in Mike's work with Partial (deposit 7).
Suggest review of Mike's fix to 1) and see if we can ferret out the solution.

[orange]

Last thing tonight.

I looked again at Mikes V12 Partial. I now see his macro to delete existing .

I commented the macro and revamped the vba

Sub ClearPartialElements()

Const sql_Delete As String = _
"DELETE * From tblPartialElementsLink where PartialSecondaryID_FK = p0 "
10 With CurrentDb.CreateQueryDef("", sql_Delete)
                                'Forms--formName===subformControl==control on form in subformcontrol
20 .Parameters("p0") = [Forms]![frmDeposits]![frmPartialSecondary]![PartialSecondaryID]
30 .Execute
40 End With
'DoCmd.RunMacro "mcrDeletePartial"
End Sub

that I have used with Deposit 7 Partial and it is working


That's all tonight.

[Michael91]

Hi both,

I am going to reply to all the new posts in one, to make it easier.

Orange - Apologies, I misread the tone of your previous post (#119), my mistake. I cannot open either of the links in post #125, not sure why.

I have been working on V12 at the mo, using the VBA from Mike’s 12.4 and Orange’s post #128 to fix the issues.

Regarding issue 1 –Applying the new VBA etc this now works perfectly, thank you both!

Issue 2 – I applied the same here, and it works, but for some reason I can’t get the data to appear in subform frmOsteoLinkSubform. It works fine on Mike’s V12.4, and I am using identical VBA. This is not a huge issue as I can just work from 12.4 as a base from now on, but its odd.

Issue 3 – I applied the same for this too, and now get the error:
Error 2465 (Microsoft Access can’t find the field ‘frmScienceInfo’ referred to in your expression.) in procedure btnAddScienceAnalysis_Click, line 30

Which is slightly different but still makes no sense to me - The code for all three of these should be identical aside from the form/table names. Maybe I am missing something.

Attached is my current version – 12.6 (Database v 12.6.zip (Mike sent me a 12.5 via email, that's why its jumped from 12.4 to 12.6)

[mike60smart]

Hi Michael

Issue 2 is down to the Combobox on frmOsteoLinkSubform constructed incorrectly. Now fixed.

Issue 3 is down to the name of the frmScienceInfo - it was named ScienceInfo but in the Code it was frmScienceInfo. - Now corrected.

[Michael91]

Ah thank you! I should have read it more thoroughly

[orange]

Thanks both. I will download Database v12.6 from Mike seeing that Issue 3 has been resolved.
Sorry for being so long winded in recent posts.

[Michael91]

Thanks both. I will download Database v12.6 from Mike seeing that Issue 3 has been resolved.
Sorry for being so long winded in recent posts.

No need to apologise, I appreciate all the information - seeing the processes helps make things more understandable too

[orange]

Which of you, or both have MZTools? I see line numbers and error code.
Suggest you add Option Explicit to all Modules (I did on my copy) to force declaring all variables. Finds misspellings within code.

eg: First 2 lines of every module
Option Compare Database
Option Explicit

[mike60smart]

Hi Orange
I have MZTools but sometimes forget to use it.