I have a PO Ledger Subform. One of the controls is the "Description." I am trying to standardize the entry of the data by all the users so I would like to set up the default to two different defaults based on the record being entered.
For example:
The default for the first entry for each project should be "Original PO" then,
On subsequent entries I want the dafault to be "[CO] - [Change]"
This will also help with data entry since we do not have near as many projects with Change Orders, so the "Original PO" would be entered automatically for them.
I think I could either put the code in the "Before Insert" on the POLedger subform or as an IFF on the Default for the Control itself.
I'm not sure which is better or which would be simpler, so any help on this would be appreciated.
Thank You,
Michael

Hi Michael,

I am thinking that it is best to use the form's Load event to change the DefaultValue property of the Description field.

Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)

At the risk of borrowing trouble, what about the situation where the "original" record is entered, a subsequent record (or records) is entered, then the original is deleted (it was a mistake, it belonged to a different "parent"). In that situation, the "first" (if you measure order by date/time entered) will have the wrong description.

An alternate approach that could avoid this, and would avoid having to (potentially) enter many duplicate text strings would be to use a query to find the "first" (again, a sense of order only makes sense if you tell Access HOW to order them) and show a "description", and to find any others and show a different description. That way, you (and Access) don't have to store all that redundant information.

Good luck!

Jeff,
Thanks for think that through. I think the chances of that happening will be extremely slim and they can type "Original PO" in the control if they do figure out how to screw it up.

theDBGuy
I tried this:

Private Sub Form_Load()
If RecordCount = 0 Then
Me.PODesc.DefaultValue = "Original PO"
Else
Me.PODesc.DefaultValue = "CO - Change"
End If
End Sub

The result is that the Desription field becomes #Name? like it is trying to change the field that is trying to look up information from a field names "Original PO" or "CO - Change"

Hi,


Try setting the Default Value at the table level to "Original PO." Then, modify your code to just change the DefaultValue if there are existing records already. For example:

If Me.Recordset.RecordCount>0 Then
Me.PODesc.DefaulValue="CO - Change"
End If

(untested)
Hope that helps...

I set the Default to "Original PO" and changed the code.
If Me.Recordset.RecordCount > 0 Then
Me.PODesc.DefaulValue="CO - Change"
End If

Result: Only "Original PO" shows up in the description field no matter how many CO's are on a project.

Tested the code by changing the default back to null and adding the else side to the code:
If Me.Recordset.RecordCount > 0 Then
Me.PODesc.DefaultValue = "CO - Change"
Else
Me.PODesc.DefaultValue = "Original PO"
End If

Result: Back to the result where the Desription field becomes #Name?

Hi,

Since you are using a subform, we'll need to make sure you're using the right events and correct syntax.

Can you post a zip copy of your db with test data? Thanks.

The amount of time it would take to dummy up this database (32 tables feeding 76 queries) is not worth the effort for this one field.
I tried to dummy it up once before and got too frustrated to finish the task.

The best I could offer (in a timely maner) is to explain the Forms and show a screen shot.

The Main form is called Amber. On Amber is a Frame (Called MainFrame).
In the MainFrame a form named Project Information opens with several subforms.
The POLedger subForm is one of these.
Usually in queries I need to refer to the a control on the Project Information Form as "[Forms]![Amber]![MainFrame].[Form]![JobID]"
I'm not sure that I've ever needed to refer to a subform on the form though.

Also Note: I tried opening the subForm stand alone and it still came up with the #Name? in the control. This way, I think I can rule out the subforms location.

If we need more than that, I might have to put this off for now (It's not a critical item and I have a lot of other things that need my attention).
Let me know if this helps.
Thanks Again,
Michael

Hi,

I understand. See if the attached demo helps...

Perfect! You are a Genuis!
Thank you and have a great night!
Michael

Hi Michael,

(IMG:style_emoticons/default/yw.gif)

Jeff and I are happy to help. Good luck with your project.

After months of this working perfectly, I am suddenly getting an error related to this default. The problem code goes back to this thread.

The error I am getting is:
Run-time Error '429':
ActiveX component can't create object

The problem section of the code:
If Me.POLedgerSub.Form.Recordset.RecordCount > 0 Then
Me.POLedgerSub.Form!PODesc.DefaultValue = """CO - Change"""
Else
Me.POLedgerSub.Form!PODesc.DefaultValue = """Original PO"""
End If

I looked at the referances and none were noted as "missing." I even tried adding a couple of the Microsoft ActiveX references (in case one became unchecked (somehow). Note: I haven't made changes to the database.
How do I know what reference library I need to to add or update? Or am I looking at the wrong place/problem completely?

Hi Michael,

Were there any changes made to the machine recently? Do you get the same error if you use a different machine?

Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)

theDBguy
Well, I called Tech Support to find out if they pushed something out this morning.
The Local guy called me up and I could not get him to STOP, and he restore my settings to last Friday.
There was something lised in the system updates fro this morning, but I could not tell was before he did a system restore.
(To make this story shorter) It currently is working again, I guess I'll find out tomorrow what was installed, and if it gets in the way again.
(More to follow)
Thank you!
Michael

Hi Michael,

Thanks for the update. Glad to hear you got it working again.

Good luck with your project. (IMG:style_emoticons/default/thumbup.gif)