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

 

[BuckRodgers]

You didn't really specify whether you were concerned with a NEW Record or if PName was left blank by the user...

If you're checking whether you are on a new record you can use the following code which you would put in the Form_Current procedure:

If Me.NewRecord Then
JNum = JNum + 1
Else
JNum = 1
End If

If you're just checking whether the user has left a field blank (or Null) then use this, which could go in many different places depending on when you need to update JNum:
If IsNull(PName) Then
JNum = JNum + 1
Else
JNum = 1
End If

Also you should set the JNum field properties if you don't want the user to be able to edit the value:
Locked = Yes
Enabled = No

 

[Sloaner]

Actually I'm concern with a New record; but a new record in this case is defined as one where the combination Cname(company name) and PName (Production Name) are unique and do not yet exist.
Basically If the user select a Company then types in a Production name for that company then the Job number should automatically be calculated as indicated above.
So if CName and PName are not found in the table then JNun = 1 else JNum = JNum + 1. My question then becomes ... how to specify new record as the combination of Cname and PName.

 

[PHV]

Something like this in the AfterUpdate event procedure of Pname ?
Me!JNum = 1 + DCount("*", "Ptable", "Cname='" & Me!Cname & "' AND Pname='" & Me!Pname & "'")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Sloaner]

Can you please explain what that expression does.
I have a "Data type mismatch in criteria expression" error.

 

[PHV]

Are either Cname or Pname defined as numeric in Ptable ?
Can you please explain what that expression does
Take a look at the DCount function in the VBA help
You may try this:
Me!JNum = 1 + Nz(DCount("*", "Ptable", "Cname='" & Me!Cname & "' AND Pname='" & Me!Pname & "'") , 0)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Sloaner]

Yes Cname is a number and Pname is Text.

I did look at the Dcount in Help and am a little confused by the quotes in the expression.
why do you have this & "'" at the end of your expression?
when I removed it I got the following error
Syntax error in string in query expression CName = '5' AND Pname='FW164'

Otherwise if I leave that in there I get Data type mismastch in the expression.

 

[PHV]

Cname is a number and Pname is Text
So, this should be correct:
Me!JNum = 1 + DCount("*", "Ptable", "Cname=" & Me!Cname & " AND Pname='" & Me!Pname & "'")
In Jet SQL the literals are recognized:
- as text if surrounded by quotes (' or ")
- as date if surrounded by sharp (#)
- as numeric otherwise

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Sloaner]

That worked but I don't see a refreshed value for JNum unless I get out of the form and re-enter.

I tried to add this after my if statement
Me!JNUM.Requery

or this inside of my If statement
Me!JNum.ControlSource = 1 + RecCount

but I get the Object doesn't support this propert or method error message
otherwise without these it shows the correct value after exiting the form and reloading it.
JNum is a Textbox ... shouldn't that also work? I checked in help and see no reason why this shouldn't be.
I have Enabled set to NO and Locked set to YES so users don't enter values in that field. Could that be the reason why?

 

[Sloaner]

Any suggestions Anyone?... for the problem I listed above...

 

[TheAceMan1]

How are ya Sloaner . . . . .

Try adding [blue]Me.Recalc[/blue] to the [purple]Current Event[/purple] of the form.

See Ya! . . . . . .

 

[Sloaner]

Thanks AceMan1...
but I got the requery to work... I had a typo in the Control's name..!...

 

[TheAceMan1]

Sloaner . . . . .

Glad ya got it working . . . .

For future reference, ReCalc does all unbound on the form . . .

See Ya! . . . . . .

 

[Sloaner]

Ok AceMan1...Anybody..
I take that back... This is not working..I made the changes and I know that it worked... I just went back to the form and now something freaky going on.
I bring the form up with a new blank record.. so I decided to go back to check out the other existing records and sure enough.. one of the combo fields is blank. I checked the table and the values in the table are correct ... so the problem is definetely the form refresh the data to be displayed. The strange part is that it works for one and not the other. Both combos work off of the same table displaying productions for companies. it's just that one section shows Local companies and the other foreign companies... but all company names(local & foreign) and productions are stored in the same table. I'm actually saving the company IDs (i.e. the bound column is 1) in both cases... the only difference is that the results of the Rowsource select(which is exactly the same) is saved in different field names.
The combos work fine when entering a new record... The problem is when going back to view the data entered the second combo box shows up blank. I tried hitting Refresh...got nothing...I tried to reselect the company and Production for that section and it reloaded...but when I went back it was gone again....

Please heeelllppp!...

Almost crazy.

 

[PHV]

Have you tried to requery your combos in order in the Current event procedure of the form ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Sloaner]

I just did and it works the same... The same company,production combo that was refreshed before showed up on the screen but the second set showed up the same with Production blank.

 

[PHV]

May I suggest you post the RowSource of your combos and you explain, if applicable, the dependancies ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Sloaner]

OK... here it is ...
LOCAL
Company RowSource:

SELECT [Company].[CompanyID], [Company].[CompanyName] FROM Company;

LOCAL
Production Rowsource:

SELECT [ProductionID], [ProductionName] FROM Productions WHERE ProductionID=[FORMS]![Order]![LCompanyID] ORDER BY [ProductionName];

FOREIGN
Company RowSource:

SELECT [Company].[CompanyID], [Company].[CompanyName] FROM Company;

FOREIGN
Production Rowsource:

SELECT [ProductionID], [ProductionName] FROM Productions WHERE ProductionID=[FORMS]![Order]![FcompanyID] ORDER BY [ProductionName];

I also have in the local company afterupdate event procedure the following:

Private Sub LCompanyID_AfterUpdate()
LProductionID.RowSource = "SELECT ProductionID,ProductionName FROM Productions WHERE CompanyID=" & LCompanyID & " ORDER BY 1"
LProductionID.Requery
LProductionID.SetFocus
LProductionID.Dropdown
End Sub

and in the Local production afterupdate event procedure I have the following:

Private Sub LProductionID_AfterUpdate()
LAuthor.RowSource = "SELECT AuthID,[FirstName]) & ' ' &  Trim([LastName]) AS Name FROM Authors WHERE AuthID=" & LProductionID & " ORDER BY 1"
LAuthor.Requery
LAuthor.SetFocus
LAuthor.Dropdown
End Sub

In the Foreign company section something similar follows;

Private Sub FCompanyID_AfterUpdate()
FProductionID.RowSource = "SELECT ProductionID,ProductionName FROM Productions WHERE CompanyID=" & FCompanyID & " ORDER BY 1"
FProductionID.Requery
FProductionID.SetFocus
FProductionID.Dropdown
End Sub

In the Foreign Production section I also have the following.

Private Sub FProductionID_AfterUpdate()
FAuthor.RowSource = "SELECT AuthID,[FirstName]) & ' ' &  Trim([LastName]) AS Name FROM Authors WHERE AuthID=" & FProductionID & " ORDER BY 1"
FAuthor.Requery
FAuthor.SetFocus
FAuthor.Dropdown
End Sub

Of course I also just recently added :

Private Sub Form_Current()
LCompanyID.Requery
LProductionID.Requery
FCompanyID.Requery
FProductionID.Requery
End Sub

and that's all I have...

Thanks

 

[TheAceMan1]

[blue]decided to go back to check out the other existing records and sure enough.. [purple]one of the combo fields is blank[/purple].[/blue]

The above is telling me that the Combobox in question is [blue]unbound[/blue]. Is this correct?

If it is unbound, your gonna have to preselect in the OnCurrent event . . . . . and out of curiosity, what would be the point?

See Ya! . . . . . .

 

[Sloaner]

Actually those are all bound comboboxes.

LCompanyID
Column count 2
Column widths 0";1.3
Bound column 1

LProductionID
Column count 2
Column widths 0";2
Bound column 1

LAuthor
Column count 2
Column widths 0";3
Bound column 2

FCompanyID
Column count 2
Column widths 0";1.3
Bound column 1

FProductionID
Column count 2
Column widths 0";2
Bound column 1

FAuthor
Column count 2
Column widths 0";3
Bound column 2