Displaying a list of entered records that updates with each entry

[June7]

Don't use the table name prefix.
=Sum(Nz([StockQtyMade])-Nz([StockQtySold])+Nz([StockQtyShrink]))

Try populating a field then save the record then disable the Allow Additions.


Me.someFieldName = some value
DoCmd.RunCommand acCmdSaveRecord

[epb]

Hello Again,

Summing the columns is now working after changing the "!" to a "." in each instance.

I see that it also works as you have said, "=Sum(Nz([StockQtyMade])-Nz([StockQtySold])+Nz([StockQtyShrink]))"

Thanks.


I will try what yo suggest:

Try populating a field then save the record then disable the Allow Additions.
Me.someFieldName = some value
DoCmd.RunCommand acCmdSaveRecord

[epb]

Everything appears to be working that we have discussed. The add record is working when the command button is clicked and I have also added default values that are pulled from the last record entered.

This is great. Thank you!!

My next problem is when the form is opening or loading. On initial opening of the form I would like to default the Main Form to the last record entered in the subform. Do I need to set the unbound combobox to this value also.

I have tried the following:

1) Since the "tblInventor Query" sorts descending on the TransactionID I tried setting the default value of the combobox to find the last record

= DLookup("[ID]", "[tblInventory Query]", "[ID] = Forms![InventorySubFrm].PartID")

This is not working. I have also tried looking up the [PrintNo] instead of the [PartID]. The combobox is empty when the form appears.

2) I have also tried doing a DLookup in the form Open Event but it can not find [InventorySubFrm].

I would say that one of my biggest problems is referencing forms and subforms in the code. I had code that was recommended for default values and it would not take the [field name] on what was being set but would work when I used the label name of the field. When to use "!" versus "." is also very murky. I have read on the subject but still have some problems producing solid code.

Any input would be appreciated on how to set the Main From record to the last subform record WHEN INITIALLY OPENING the MAIN FORM.

And how do you set the unbound combo to goto a record?

Is there a way to store a bookmark after the form is closed?

[June7]

Try: DoCmd.GoToRecord , , acLast

General guidelines I follow:
In VBA the . will provoke intellisense popup tips although either character will usually work.
In Access, use . when referencing properties and ! when followed by names.

Use criteria in the combo to either set Filter property or go to a record. Example for the latter:
Me.RecordsetClone.FindFirst "LabNum='" & Me.tbxLABNUM & "'"
If Me.RecordsetClone.NoMatch = False Then
Me.Bookmark = Me.RecordsetClone.Bookmark
End If

Why save bookmark? It has no meaning after form closes.

[epb]

Hello June7,

Thank you again for all this information. I was having problems with how to check for for an actual record using ".Findfirst".

Why save bookmark? It has no meaning after form closes.

My question is more on what are the options I have to restore a Form to some particular record I choose or restore the Form based on last child record created.

Will a "DoCmd.GoToRecord, , acLast" done in the subform set the Parent Form to the same record as the linked foreign key? Because what I am looking for is to set the Parent table to the last created record in the child table WHEN FIRST OPENING THE FORM using the autonumber [ID] Primary Key and the [PartID] foreign key in the child table.

Of course my problem is, when you first open the Main form it defaults to the First record in the Parent table and the work being done should be based on the last transaction in the Child table.

What if I want to go to a specific record with [PartID] in the child table and open to that record on OPENING THE FORM? I have tried just setting the Main form based on a certain ID using code but have not figured out how to do this. Like when testing the database, one or two part numbers have child records entered and I want to default to one of those records on opening the form. How do I say Open the main form at record [ID] = 270??

Eventually, I want to have a table that stores default info for each user and in some cases where they left off in a particular table. But for now I just want the Main from to open at the last child record and initialize the other fields that are references to the subform.

Also, How do I mark this thread as answered? I do appreciate your help. Thanks.

[June7]

The form can be opened with filter/search criteria. How are you opening the form - button click on another form? If the first form has a control for selection of criteria, such as ID, the code in button Click could be like:
DoCmd.OpenForm "formname", , , "ID=" & Me.tbxID
or use the RecordsetClone in Open or Load event, I forget which is best.

Otherwise, use the GoTo, , acLast method you show.

Mark answered with option under Thread Tools at top of thread page.