Calculated field in form 1

[Sloaner]

I have a form with a field name PName and JNum. How can I specify JNum to be =1 if PName is new (i.e. doesn't yet exist in the table Ptable) else JNum = JNum + 1.
Should this be in the AfterUpdate event procedure of PName?
If so what would the code look like ... I can't think of how to specify the part to look into the table for PName to be new...the rest is probably JNum = 1 else JNum= JNum + 1.
I would also like to know how to get JNum to not be editable so the user cannot type over the calculated value.

Any suggestions?

Thanks in advance

 

[TheAceMan1]

Sloaner . . . .

I believe I see an ambiguity here.

What fields are the comboxes bound to?

See Ya! . . . . . .

 

[Sloaner]

LCompanyID is bound to CompanyID from Company
LProductionID is bound to ProductionID from Productions

FCompanyID is bound to CompanyID from Company
FProductionID is bound to ProductionID from Productions

 

[TheAceMan1]

OK Sloaner . . . . . Here's the problem . . . . (sorry I didn't ge tback earlier . . . . got stuck in the Matrix . . . . . . . haha . . . ;-) )

[blue]Ya can't have two bound comboboxes trying to set the same field to different records.[/blue]

Think about this before yo reply! As it is, you have no way of decerning Local from Foreign. Your gonna have to make some table chnages your not up to, but, I promise, if ya don't, you'll regret it tomorrow. So . . . . think about what your doing and let us know . . . . .

See Ya! . . . . . .

 

[Sloaner]

Hello again Aceman,

Glad to see you made it back ok...
Now... I can see how this might be confusing... and though I'm not disputing your reasoning...I wanted to know what your thoughts are on the fact that it works for Company but not for productions.... they're all bound columns..in a way it's the same scenario twice... It doesn't cause a conflict for Fcompany & LCompany but it does not work for FProductions & LProductions. Either way I'm going to try to rework these table structures..

Hope hear to from you soon ...

Thanks
Sloaner %-)

 

[TheAceMan1]

[blue]LCompanyID is bound to CompanyID from Company
FCompanyID is bound to CompanyID from Company

LProductionID is bound to ProductionID from Productions
FProductionID is bound to ProductionID from Productions[/blue]

Yes this can work, but since their bound to the same field, when you change FProductionID, LProductionID will automatically change as well, and vice versa. This is just an observation and one I consider a bad idea (just my opinion), which more than likely will cause you other troubles down the road.

The above is a little off the beaten path and still doesn't explain the [blue]Blank [purple]Bound[/purple] Field[/blue](you didn't say which one) on a previously stored record.

[blue]This is where I'm stuck . . . .
One bound combobox shows a value, the alternate combobox bound to the same field does not![/blue]

This is not consistant behavior for two comboboxes bound to the same field . . . .

See Ya! . . . . . .

 

[Sloaner]

There are 2 sections as you know The local and the foreign...
The local comes first on the screen... then the foreign
... even thou they both work as they're filled in and allow the user to see the appropriate choices... I hope you understand that the problem is in the display after the record has been written correctly to the table...when I go back and check, the value in the order table is the correct one. The record has the LCompanyID and FCompanyID as 1 which is correct, the LProductionID and FProductionId are 1 and 6 which are also correct and map correctly to the Production names that I had selected. If I open the Order form to take a look at that record the screen displays the LCompanyID, LproductionID correctly by mapping back the corresponding names. In the FCompanyId field the correct name is also displayed however the FProduction field is blank.

Also you say

when you change FProductionID, LProductionID will automatically change as well, and vice versa.

That's not happening ... as evidenced by the example above.

Is there anything else you can think of that could be affecting this?

 

[TheAceMan1]

[blue]There are 2 [purple]sections[/purple] as you know The local and the foreign...
The local comes first on the screen... then the foreign[/blue]

You've mentioned [purple]sections[/purple] several times throughout this thread.

Just what do you mean by sections?

See Ya! . . . . . .

 

[Sloaner]

Sections don't refer to any kind of coding....It's just a way to refer to the 3 comboboxes for the Foreign category and the Local category. There are 2 labels to let the user know when they are selecting a company,Production & author in these areas of the screen whether they're doing that for a company that is local or aboard.

 

[TheAceMan1]

OK Sloaner . . . . .

Rather than drag this out. Is it possible you can send a copy of the DB to me for evaluation. Besides, I'm curious about a number of things. See my profile . . . . .

If you can, remember, any correspondance is to be carried on here at TT.

See Ya! . . . . . .

 

[Sloaner]

Hello again Aceman

I just forwarded a copy of what I have so far..I was at it again for a while today... and well!.. I sent you what I 've done... you'll notice a slight discrepancy in what I said was working and is actually working.
Well bottom line is !... I messed things up today...
Basically I'm just trying to get the comboboxes to work in the Order form.


Thanks
Sloaner
"Still a Rookie"

 

[TheAceMan1]

Sloaner . . . . .

Trying to think of a way to be diplomatic, decided to just be direct.

No relationships is your biggest killer, and the coding completely blew my mind.

Best thing you can do is start over, remove all coding, and establish some relationships first. Besides the fact that its going to be a continuous headache, [purple]I'm not gonna help ya travel down the wrong path[/purple].

Here are some references on relationships:

http://support.microsoft.com/default.aspx?scid=kb;en-us;304467&Product=acc2000

http://www.health.ufl.edu/itcenter/.../DELETEAccessTables&Relationships-Handout.pdf

Check them out and decide how ya want your relationships. If you have any trouble, setup a post in the [blue]Tables & Relationships Forum[/blue].

GoodLuck! . . . . .

See Ya! . . . . . .

 

[Sloaner]

There's no need to be diplomatic... it's my fisrt attempt at creating a database as I'm sure you could tell.
All constructive criticisms are welcome. How else can I improve? If the relationships are not correctly established then I'll look into fixing them. I appreciate your honesty and thank you.


Sloaner