Trakr Purchase Order Module can't find the object 'TimeCardCatAndProdSub'

[burrina]

I have a Purchase Order db I am building and I have a sub-form on my main form named TimeCardCatAndProdSub and the main form is named TimeCards


I am getting the error message can't find the object 'TimeCardCatAndProdSub' when I try and run the code behind a command button.


Here is the code being run;


Private Sub Command19_Click()
On Error GoTo Err_Command19_Click

If IsNull([Cat]) Or IsNull([QtyA]) Or IsNull([Prod]) Then ' code to make sure fields have a value
DoCmd.GoToControl "QtyA"
MsgBox "Quantity, Category and Material" & Chr(13) & Chr(10) & "need to be filled in."

Else:
DoCmd.SetWarnings False
DoCmd.OpenQuery "TimeCardCatAndProdSub", acNormal, acEdit
DoCmd.Close acQuery, "TimeCardCatAndProdSub"
DoCmd.SetWarnings True
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Forms!TimeCards.SetFocus
[Forms]![TimeCards]![TimeCardCatAndProdSub].Form![Cat] = Null 'this is the sub-form
[Forms]![TimeCards]![TimeCardCatAndProdSub].Form![Prod] = Null


[Forms]![TimeCards]![TimeCardCatAndProdSub].Form![QtyA] = Null
[Forms]![TimeCards]![TimeCardCatAndProdSub].Form![OrdDte] = Date

DoCmd.GoToRecord , "TimeCards", acNext 'referes to main form
DoCmd.GoToRecord , "TimeCards", acPrevious
End If
Exit_Command19_Click:
Exit Sub

Err_Command19_Click:
MsgBox err.Description
Resume Exit_Command19_Click

End Sub

__________________________________________________ ____________________________
I have tried looking for misspelled fields or bad code with no luck.

[June7]

I don't understand the behavior coded in this procedure.

The command button is on the main form?

Why open a query?

Cat, Prod, QtyA are fields on main and subform - why? You want to set the fields of subform to null - why?

Why the acNext and acPrevious actions?

[burrina]

The query is necessary to lookup the category,product and price details. After the item has been priced out then it is added as a record on the sub-form and then the first record becomes null as it should be.

The next and previous operations just saves the record because of the design.


The command button is on the sub-form name TimeCardCatAndProdSub

[June7]

I still don't understand.

How does opening then closing a query 'lookup' data? Show the sql statement.

Why 'should' first record be null?

Have you tried: DoCmd.RunCommand acCmdSaveRecord

If the button and code are on subform then maybe simply:
Me.Cat = Null

[burrina]

SELECT Orders.[Order ID], Orders.[Customer ID], Orders.TimeID, Orders.OrderDiscount, Orders.Category, Orders.Product, Orders.[% 1], Orders.Price, [Price]*(1+[% 1]) AS ExpPriMarkUp, Orders.Qty, [ExpPriMarkUp]*[Qty] AS ExpTotCost, Orders.Tax, [ExpTotCost]*[Tax] AS ExpTax, IIf([Tax]=0,[ExpTotCost],0) AS ExpTotCostNoTx, IIf([Tax]>0,[ExpTotCost]*(1+[Tax]),0) AS ExpTotCostTx, Orders.[Order Date]
FROM Orders
ORDER BY Orders.Category, Orders.Product;

[June7]

That is just a SELECT query. AFAIK, it is not necessary to 'open' a SELECT query to force the necessary calcs. This query is the RecordSource for the subform? Are you trying to force a refresh of the subform? Use Me.Requery or Me.Refresh instead.

Want to provide db for analysis?

[burrina]

Ok, here is a downsized version of db. I will delete it rather quickly so don't delay it getting it.


Thanks,


P.S. Here is a pic of the form where data entry is made for the sub-form, i.e. the need for a query.

[June7]

Please explain why the QtyA, Prod, Cat, Tax, Date, [%1A] controls are not bound to fields Qty, Category, Product, Tax, Date, [%1] of Orders table?

BTW, advise no spaces, special characters, punctuation (underscore is exception) in any names, nor reserved words (such as Date) as names.

[burrina]

This form is not a data entry form but rather a form where it gets its data from the main add products form with a sub-form of its own used to enter all category/product/description/details and price information.

Please open form in design mode an scroll down and see where it gets its TimeID ProductID so it can bind these to the main form which is TimeCards and also use the tables categories and orders tables for data.

Calculations are performed on the sub-form as well as the Detail section of the form as well.

The DB for this purpose only uses the tables, Categories,Orders and Products . Look at this code:

Private Sub Cat_AfterUpdate()
Me.Prod.RowSource = "SELECT Products.[Product Name], Products.[Unit Price], Products.[Category ID] FROM" & _
" Products WHERE (((Products.[Category ID]) = [Cat]))" & _
" ORDER BY Products.[Product Name];"
Me.Prod = Null
If [Cat] = "Ship" Then
[% 1A] = 0#
[TaxA] = 0#
Else: [% 1A] = Forms!TimeCards!NameB.Column(7)
[TaxA] = [TaxA].DefaultValue
End If

End Sub

Private Sub Ctl__1A_AfterUpdate()
[% 1A].DefaultValue = [% 1A]
End Sub

[burrina]

Notice how code fires for a moment when you enter a % Look at the totals below and wee how they flash the new amount but then disappear.

[June7]

Now I see the two sets of controls. I saw only the unbound ones before. You want input through the unbound controls in the header? But the bound controls in Detail are available. This looks confusing to me. Why not just have users enter directly to the Detail record?

The AddItem button errors on the DoCmd.OpenQuery. There is no query by that name.

There is also no code to save the data from the unbound controls to the fields of RecordSource, if that is what you want to do.

I eliminated the lines concerned with the query and I get an error message about compacting the db. Removed the SaveRecord code and nothing errors but no data saved to Orders table.

OrderID combobox on TimeCards has 'no valid field' error. Price textbox in Detail section has 'no valid field' error.

Code that does not error now looks like:

If IsNull([Cat]) Or IsNull([QtyA]) Or IsNull([Prod]) Then
    Me.QtyA.SetFocus
    MsgBox "Quantity, Category and Material" & Chr(13) & Chr(10) & "need to be filled in."
Else
    Form_TimeCards.SetFocus
    Me.Cat = Null
    Me.Prod = Null
    Me.QtyA = Null
    Me.OrdDte = Date
End If

Clarify exactly what behavior you want from this form. Do you want values entered in the unbound controls to be saved to a new record in Orders table? Do you want to allow users to edit the Detail records?

[burrina]

Here is my query that I forgot about, Sorry! Anyway, it updates but then shows errors on the sub-form. Update the old code to reflect this query and notice results when you add item and click on Add Item on the TimeCardCatAndProdSub sub-form.



NEVERMIND: I solved it. Everything works correctly now. I had to change Textbox Price under details section to ExpPriMarkUp . It was a spelling thing.

I think that is it. Will Beta test just a little more and reply back.

Thanks,