Value of Field In Edit Mode

[Guest]

How do I grab the value of a field, on a new record (I'm treating that as
..OldValue), while sitting on a -different- field? I get an error that I must
Save before going to the 1st field. Another error says I can't get the value
for the 1st field, unless it has the focus. Code is below.

Explanation:
I am working on a routine that will query a ParentProjectID and a ProjectID
from my detail form, for a match in the main project -table-.

NOTE: special situation - The user can enter -any- related project id in the
detail, for any other related project, that they loaded in the header. This
is sort of an expanded 1-M. It was done as a convenience for the user not to
have to load 4 headers to make entries into 4 detail forms.

Parent Project
1 1
1 2
1 3
1 4
E.G., if projects 1, 2, 3, and 4 are related, and 1 happens to be the
parent, they can load up project 3 (header) and create entries for any of
projects 1 - 4.

I fill the Parent field, in the detail form, for them.
The header keys are the parentid -and the- childid. The detail keys are the
parentid, childid, and the ItemNo (line number-calc'd by me).

If there is a match, then the entry in the detail form is ok.
If there is no match, then I cancel the edit in the detail form.

The ParentProjectID is Locked and Disabled. The current field is the
ProjectID.
I take care of them in the code.

When I try to setfocus to the parent, so I can grab its .OldValue, I get an
error that I must save the record, before I can go to that field.

What is the best way to determine the value of the ParentProjectID.

Detail form fields are:
ParentProjectID (K), ProjectID (K), Item (K), <other fields>.
Main table fields are ParentProjectID (K), ProjectID (K), <other fields>.

My code is below:
<from the Detail Form's, BeforeUpdate event>
....
'Verify that the projectid is part of the family of the parentprojectid
Me.ParentProjectID.Locked = False
Me.ParentProjectID.Enabled = True
'xxx error says i have to Save before going to the ParentProjectID field?
Me.ParentProjectID.SetFocus
varParentProjectID = Me.ParentProjectID.OldValue
Me.ParentProjectID.Locked = True
Me.ParentProjectID.Enabled = False

If VerifyStarsProjectIDs(varParentProjectID, varProjectID) = False Then

Cancel = True
Exit Sub

End If

FYI:
Public Function VerifyStarsProjectIDs(varParentProjectID As Variant,
varProjectID As Variant) As Boolean
Dim currConn As New ADODB.Connection
Dim rstStarsProject As New ADODB.Recordset
Dim currDB As Database
Dim strSQL As String

Set currDB = CurrentDb
Set currConn = New ADODB.Connection

With currConn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "data source= " & currDB.Name
.Open
End With

Set rstStarsProject = New ADODB.Recordset
strSQL = "SELECT ParentProjectID, ProjectID FROM StarsProject" & _
"WHERE ParentProjectID = " & varParentProjectID & "AND
ProjectID = " & varProjectID

With rstStarsProject
.CursorType = adOpenForwardOnly
.LockType = adLockReadOnly
.Open "strSQL", currConn

'determine if the ProjectID being entered in Issues is part of the
Family of the Parent
If .BOF Then 'did not find the ProjectID in the current Family

MsgBox "The Project ID you entered is not part of the Parent ID
for this Family." & vbCrLf & vbCrLf & _
"Please try again." _
, vbCritical, "Entry Error! Invalid Project ID"
.Close
Set rstStarsProject = Nothing
currConn.Close
Set currConn = Nothing
currDB.Close
Set currDB = Nothing
VerifyStarsProjectIDs = False
Exit Function

Else 'the entered ProjectID does exist in the Parent

VerifyStarsProjectIDs = True

End If

End With

End Function

 

[Guest]

I grabbed the Parent Project ID from the parent form, instead of the Detail
line,

varParentProjectID = Parent.ParentProjectID.OldValue 'c/b regular value
also-will change

and fixed the sql statement, but when it tries to Open the recordset, I get
an error that it is an invalid SQL statement and they were expecting DELETE,
INSERT, PROCEDURE, SELECT, or UPDATE.

New Code:
strSQL = "SELECT ParentProjectID, ProjectID FROM StarsProject" & _
" WHERE ParentProjectID = " & varParentProjectID & " AND
ProjectID = " & varProjectID 'the Immed window looks good

With rstStarsProject
.CursorType = adOpenForwardOnly
.LockType = adLockReadOnly
.Open "strSQL", currConn