Trouble Counting Sub Form Table Records

[Thumper75]

Greetings all, so obviously I am missing something. :banghead: I have a form in which I have placed a sub form table. For the purposes of this post, lets just run with the counting issue. So what I am attempting to do is to have the code count the number of records in the table. Please remember that this is a subform. The code moves through the DoCmd.GoToControl method with no problem. However when it gets to the DCount function it stops and tells be that the "tbl_ReqReview" does not exist and insists that I check the spelling. I know the table exists, I know the table is spelled correctly but it just won't move past that. :banghead: I have tried various combinations including "Forms!frm_Refill!tbl_ReqReview.Form" but to no avail. My only conclusion is that I am missing something in the actual name of the table.

It occurred to me that the DCount is actually looking for a table as a dbase object instead of a part of a form, which leaves me back where I started . Can anyone help determine how to count the number of records in a sub form table? I have used similar code in the past, so I know I'm on the right track but am having a hard time specifically identifying the actual table.

I saw a similar post to what I am attempting in another thread on this site, but the person never posted their final code so I am at a complete loss.

DoCmd.GoToControl ("tbl_ReqReview") 'Put the focus on the subform to enter data
Dim RecCount As Integer
RecCount = DCount("*", "tbl_ReqReview")
If RecCount < 1 Then
    MsgBox "There are currently No Records to be viewed."
    Cancel = True
End If

Thanks in advance for any help that you can provide.

 

[Galaxiom]

Forget the domain function.

Use this ControlSource expression in a textbox in the footer of the subform.

= Count(*)

It doesn't matter if it can't be seen if you are using Datasheet View. Simply refer to the textbox from a textbox on the main form to display the value.

 

[Thumper75]

Galaxiom, Thanks for the suggestion, that worked. Now I have to solve the other part of my problem which is keeping track of all the requests.

So now let me try and put into broad detail what I am trying to do. Working with the same form, here is the goal.

(Tried to insert photo but I'm too new still)

The user enters in the location in the main form Location text box. When they tab away from the control the form displays the CPN, MPN, Description and Refil Qty. This part of the form works perfectly. When the user clicks the "ADD to Request" button the information is transferred to and stored in the tbl_ReqReview table as you see here.

Using the code below I am attempting to write multiple rows that will later be saved to the actual Refill table.

What I am running into is that I can get the first row to work just fine, and the code returns to the first field on the main form, when I try to enter the second row to the subform table, it overwrites the row that was just entered. So despite my best efforts, I can only get one record into the table at a time. The ultimate goal is for the user to, one after another, enter in the location number for the parts that we want to refill, then to review the table and make sure that everything has been entered and then submit them to the table which will update the table with the correct information. There are other parts of the form, that don't change, that will be written to the table at that time.

DoCmd.GoToControl ("tbl_ReqReview") 'Put the focus on the subform to enter data
With Me.tbl_ReqReview
    If Forms!frm_Refill!tbl_ReqReview.Form.txt_count < 1 Then
        Forms!frm_Refill!tbl_ReqReview.Form.txt_LOCATION.SetFocus
        Forms!frm_Refill!tbl_ReqReview.Form.txt_LOCATION = PassLocation
        Forms!frm_Refill!tbl_ReqReview.Form.txt_CPN = PassCPN
        Forms!frm_Refill!tbl_ReqReview.Form.txt_MPN = PassMPN
        Forms!frm_Refill!tbl_ReqReview.Form.txt_DESC = PassDesc
        Forms!frm_Refill!tbl_ReqReview.Form.txt_Unit = DLookup("[UNIT]", "tbl_Parts", "[LOCATION] =" & "'" & PassLocation & "'")
        Forms!frm_Refill!tbl_ReqReview.Form.txt_RefQty = PassRefQty
    Else
        RunCommand acCmdRecordsGoToNew
        Forms!frm_Refill!tbl_ReqReview.Form.txt_LOCATION = PassLocation
        Forms!frm_Refill!tbl_ReqReview.Form.txt_CPN = PassCPN
        Forms!frm_Refill!tbl_ReqReview.Form.txt_MPN = PassMPN
        Forms!frm_Refill!tbl_ReqReview.Form.txt_DESC = PassDesc
        Forms!frm_Refill!tbl_ReqReview.Form.txt_Unit = DLookup("[UNIT]", "tbl_Parts", "[LOCATION] =" & "'" & PassLocation & "'")
        Forms!frm_Refill!tbl_ReqReview.Form.txt_RefQty = PassRefQty
    End If
End With

I had initially thought that I needed to do this using a recordset, but all my attempts to create one and display it in the subform failed. When I went down this method, I thought I was on the right track only to have one problem after another stop me cold in my tracks. Pragmatically it looks like it should work. Perhaps someone can suggest a table setting to look at that would prevent what I am trying to do, or a code that might accomplish the same thing.

Again, many thanks in advance to anyone that can point me in the right direction.