add a field to an existing form

[MarkRCC]

Hi.

I added a new field to an existing table and now want to populate that field from an existing form (subform actually).

I created a text box and, using the expression builder on the text box's ControlSource property, created the following syntax:

[TableName]!NewFieldName

I'm getting a #Name? message in the text box now?

Any ideas how to make the new field usable?

TIA

Mark

 

[MarkRCC]

Oops.

The ControlSource "name" that was created has an "=" sign in front of it.

=[tblBrokerMonthlyData]!BrokerFYCMS

Again, many TIAs

Mark

 

[MarkRCC]

Well...Just when you think you got it figured out.

The &quot;=&quot; sign was the problem...The Expression Builder function automatically places an &quot;=&quot; sign in front of the <tablename>!<fieldname> expression even though you don't need it...

Thanks anyway!!!

Mark

 

[MarkRCC]

Dang. I spoke too soon....For some reason it cleared, but now, while testing (nothing changed except I'm trying to figure out how to place an informational message on the screen when a certain condition is met in a SELECT CASE statement.
I put MsgBox(&quot;Please enter an amount&quot;,vbOKonly)

Any suggestions on either of my &quot;problems&quot;?

TIA

Mark

 

[MarkRCC]

Anyone?

 

[RoyVidar]

Hi!

You're kind of lonely in this thread, aren't you;-)

1 - If the table in which you added the field is the subforms recordsource, you should be able to go to the properties for the control, data tab, and select the control source for the control there.

If the recordsource of your form is a query, include the (table and) field in the query, and use the above mentioned method.

2 - think you'll have to elaborate a bit more about what you try to achieve with the select case (and where/how...), and post your current code, where it halts, what error message (if error message) etc...

Roy-Vidar

 

[MarkRCC]

Hi Roy-Vidar.

Thanks for your response. Now that you mention it, it HAS been pretty lonely...<laughing>

To answer #2 first, Here's a representative part of the CASE statement:

Select Case CmpnyName
Case &quot;Unity Life&quot;, &quot;Liberty Health&quot;, &quot;Benecaid&quot;
Me.calctxtboxBORLS.ControlSource = &quot;&quot;
Me.calctxtboxBORLS.DefaultValue = 0
Me.BrokerORLS = Me.calctxtboxBORLS
Case Else
Me.calctxtboxBORLS.ControlSource = &quot;=(parent!brkrorratea*[BrokerFYC])/100&quot;
Me.BrokerORLS = Me.calctxtboxBORLS
End Select

Basically what I, unfortunately, need to do is take the result from the unbound calculated control and save it into a bound field.

For example, using the CASE statement above, and I enter the following info:

brkrorratea = 150%
BrokerFYC = $1000.00
CmpnyName = &quot;Standard Life&quot;

CalctxtboxBORLS would then = $1500.00 ($1000 * 150%)
I would then like to place that value into the bound field, &quot;BrokerORLS&quot;.

I even tried to remove the calculated text boxes and just perform the formula calculations in the CASE statement using the bound fields. No error messages, but no data being saved either. If I used the bound field would I just use this syntax?
Me.BrokerORLS =(parent!brkrorratea*[BrokerFYC])/100?


#1: After adding a new field to a table (which IS the subform's recordsource, this new field doesn't show up in the field list for the subform (or the main form) and even if I use the full name <tblName>!<fieldName> or <tblName>.<fieldName> it doesn't recognize it.

Any thoughts on either of these?

 

[RoyVidar]

Hi!

#1 - interesting. 'gonna quiz you a litle more, might be just along the lines you've been troubleshooting, but still...

You're a 100% sure that the table you've added the field to IS the recordsource? I've sometimes seen the form/subforms created thru wizard only create a sql string for the sub forms recordsource (thereby not including new fields). Pls check that, and if it's a sql string, either go to the query builder thru the button at the right or select the table as recordsource. If it is like you say, the field should be available thru field list (and if not, I'm as stumped as you, I'm afraid.) (what happens if you create another form based on the same table? Could you perhaps try to create the subform again?)

#2
- Your'e changing the control source of the controls, not the values, you only nee to change the values. The following should normally work (without the calculated thingies) 'gonna ask about statement wihtin the Case Else below.

[tt]Select Case CmpnyName
Case &quot;Unity Life&quot;, &quot;Liberty Health&quot;, &quot;Benecaid&quot;
Me.BrokerORLS = 0
Case Else
Me.BrokerORLS = (parent!brkrorratea*[BrokerFYC])/100
End Select[/tt]

Not sure what you're trying to do with the parent thingie.

If &quot;Parent&quot; is a/the form name, change it. If you're trying to refer to the the control brkrorratea on the main form from your subform, the syntax might look like this:

[tt]Me.calctxtboxBORLS = me.parent!brkrorratea*[BrokerFYC])/100[/tt]

If the BrokerFYC also is a control on your form, consider adding the me. keyword in front (same goes for CmpnyName).

If this still doesn't work, next question is whether controls on your form have the same names as the fields they're bound to from the recordsource. You'll find plenty advices here on using a naming convention, and you'll see lot's of people refer to text controls with txtMyControl, combos with cboMyCombo... cause if a control and a field have the same name, access might confuse them. If so, rename the controls!

HTH Roy-Vidar