A new passing data question.

[LadyAmethyst]

I am working in Access 97. I have 2 forms the first form is
frmEstimate the second form is frmRevEstimate. I
currently have a command button on the frmEstimate that
passes some data from frmEstimate to frmDeposit.
Then frmDeposit loads this data when it comes up.
I can create a deposit record and pass it 3 pieces of
data from the frmEstimate and it all works just fine.

My current question is that sometimes there is a change
made to the original estimate that is entered on the
form frmRevEstimate. A deposit record was not created
by the original record (frmEstimate) and I want to be
able to load the revised information in a new deposit
record using the same form that frmEstimate uses.
How can I do this without messing up the part that is
currently working?

Thanks,

LadyAmethyst

 

[Graham Mandeno]

Hi LadyAmethyst

You should be able to use the same method (i.e. call up frmDeposit and pass
data to it) from both frmEstimate *and* frmRevEstimate. However, that
depends on how you are doing it in the first place.

Would you like to post some of your code to show how frmEstimate is opening
frmDeposit, and how it is passing the data to it?

 

[LadyAmethyst]

Hi LadyAmethyst

You should be able to use the same method (i.e. call up frmDeposit and pass
data to it) from both frmEstimate *and* frmRevEstimate. However, that
depends on how you are doing it in the first place.

Would you like to post some of your code to show how frmEstimate is opening
frmDeposit, and how it is passing the data to it?

Okay Graham, here goes.

The following is the code for the command button to
start the create process for a new deposit.

Private Sub cmdCreateDeposit_Click()
On Error GoTo Err_cmdCreateDeposit_Click

Dim strDocName As String
Dim strLinkCriteria As String

strDocName = "frmDeposit"

strLinkCriteria = "[JobID]=" & "'" & Me![JobID] & "'"
DoCmd.OpenForm strDocName, , , strLinkCriteria

Exit_cmdCreateDeposit_Click:
Exit Sub

Err_cmdCreateDeposit_Click:
MsgBox Err.Description
Resume Exit_cmdCreateDeposit_Click

End Sub

Then when the frmDeposit does an On Load it does the following:

Private Sub Form_Load()
Dim DivisionLookup As String

If Me.NewRecord Then
Me.JobID = Forms!frmEstimate!JobID
DivisionLookup = DLookup("[DivisionName]", "tblDivisions",
"DivisionID = " & [Forms]![frmEstimate]![Division])
Me.txtDivisionDisplay = DivisionLookup
Else
' The JobID is already in an existing record but
' the Division name is not stored in the tblDeposit
' this will display the correct Division Name to the screen.
DivisionLookup = DLookup("[DivisionName]", "tblDivisions",
"DivisionID = " & [Forms]![frmEstimate]![Division])
Me.txtDivisionDisplay = DivisionLookup
End If

End Sub

The JobID and the DivisionName are always the same on both
frmEstimate and frmRevEstimate so the preceding code
will work for both.

Then the following is where I am having problems. This
expression '=[Forms]![frmEstimate]![EstimateTotalCost]' is in an
unbound field 'txtEstimateTotalCost' on the deposit form.
Now if I am creating the deposit from the frmEstimate
this works fine. But if I create the deposit from the
frmRevEstimate I need -
(=[Forms]![frmRevEstimate]![RevEstimateTotalCost]) in
that textbox instead. That's where I get in trouble.

I need a way to switch between the two of them depending
on which one is creating the deposit.

LadyAmethyst

 

[Graham Mandeno]

Hi again

I suggest you pass the name of the calling form to frmDeposit via the
OpenArgs property:

DoCmd.OpenForm strDocName, , , strLinkCriteria, OpenArgs:=Me.Name

Then, in Form_Load:

Dim frmCallingForm as Form
If Not IsNull(Me.OpenArgs) then
Set frmCallingForm = Forms(Me.OpenArgs)

You can then refer to controls on the calling form as, for example:
frmCallingForm![Division]
instead of:
Forms![frmEstimate]![Division]

Where the control names are different, you can check the form name first:
If frmCallingForm.Name = "frmEstimate" Then
txtEstimateTotalCost = frmCallingForm![EstimateTotalCost]
Else
txtEstimateTotalCost = frmCallingForm![RevEstimateTotalCost]
End If

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi LadyAmethyst

You should be able to use the same method (i.e. call up frmDeposit and
pass
data to it) from both frmEstimate *and* frmRevEstimate. However, that
depends on how you are doing it in the first place.

Would you like to post some of your code to show how frmEstimate is
opening
frmDeposit, and how it is passing the data to it?

Okay Graham, here goes.

The following is the code for the command button to
start the create process for a new deposit.

Private Sub cmdCreateDeposit_Click()
On Error GoTo Err_cmdCreateDeposit_Click

Dim strDocName As String
Dim strLinkCriteria As String

strDocName = "frmDeposit"

strLinkCriteria = "[JobID]=" & "'" & Me![JobID] & "'"
DoCmd.OpenForm strDocName, , , strLinkCriteria

Exit_cmdCreateDeposit_Click:
Exit Sub

Err_cmdCreateDeposit_Click:
MsgBox Err.Description
Resume Exit_cmdCreateDeposit_Click

End Sub

Then when the frmDeposit does an On Load it does the following:

Private Sub Form_Load()
Dim DivisionLookup As String

If Me.NewRecord Then
Me.JobID = Forms!frmEstimate!JobID
DivisionLookup = DLookup("[DivisionName]", "tblDivisions",
"DivisionID = " & [Forms]![frmEstimate]![Division])
Me.txtDivisionDisplay = DivisionLookup
Else
' The JobID is already in an existing record but
' the Division name is not stored in the tblDeposit
' this will display the correct Division Name to the screen.
DivisionLookup = DLookup("[DivisionName]", "tblDivisions",
"DivisionID = " & [Forms]![frmEstimate]![Division])
Me.txtDivisionDisplay = DivisionLookup
End If

End Sub

The JobID and the DivisionName are always the same on both
frmEstimate and frmRevEstimate so the preceding code
will work for both.

Then the following is where I am having problems. This
expression '=[Forms]![frmEstimate]![EstimateTotalCost]' is in an
unbound field 'txtEstimateTotalCost' on the deposit form.
Now if I am creating the deposit from the frmEstimate
this works fine. But if I create the deposit from the
frmRevEstimate I need -
(=[Forms]![frmRevEstimate]![RevEstimateTotalCost]) in
that textbox instead. That's where I get in trouble.

I need a way to switch between the two of them depending
on which one is creating the deposit.

LadyAmethyst

 

[LadyAmethyst]

Graham,

Thanks. Works great. Sorry I didn't get back to you
sooner, but I've been under the weather.

I have another problem. I have 1 form that is supposed
to display data from 2 records (tblCustomer & tblJobSite).
The problem is that on some records the RecordSource
( an SQL query ) works fine and sometimes it dosen't
work at all and gives me a form with no data in the
form fields. (Yes there is data in the tables.)
If I open frmJobSiteInformation by itself (not through
the frmEstimate, then all the records are displayed.
Data Entry on this form is set to No because this is
not the place where data entry or corrections
need to be made. I have both a frmCustomer goes to
tblCustomer and frmJobSite goes to tblJobSite this
is the place to enter the initial information. There is
a CustomerID field in both tblCustomer and tblJobSite.
There is also the JobID field in both tblJobSite and
tblEstimate.

The form is frmJobSiteInformation. the SQL query was
actually built by the forms Wizard.

SELECT DISTINCTROW tblJobSite.CustomerID, tblJobSite.JobID,
tblJobSite.ContactName, tblJobSite.JobSiteAddress,
tblJobSite.JobSiteCity, tblJobSite.JobSiteState,
tblJobSite.JobSitePhone, tblCustomer.CompanyName,
tblCustomer.CustContactName, tblCustomer.CustomerAddress,
tblCustomer.CustomerCity, tblCustomer.CustomerState,
tblCustomer.CustomerZipCode, tblCustomer.CustomerPhone
FROM tblCustomer INNER JOIN tblJobSite ON
tblCustomer.CustomerID = tblJobSite.CustomerID;

Now there is a command button in the frmEstimate
(cmdJobSiteInformation) that opens the frmJobSiteInformation
matching the field JobID in both the frmEstimate and
the frmJobSiteInformation.

Private Sub cmdJobSiteInformation_Click()
On Error GoTo Err_cmdJobSiteInformation_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmJobSiteInformation"

stLinkCriteria = "[JobID]=" & "'" & Me![txtJobID] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdJobSiteInformation_Click:
Exit Sub

Err_cmdJobSiteInformation_Click:
MsgBox Err.Description
Resume Exit_cmdJobSiteInformation_Click

End Sub

The frmJobSiteInformation is displayed to the screen
and it has a command button (cmdPrintEstimate)
after the Estimate is printed the user returns to the
frmEstimate and can exit back to the Switchboard
or enter another estimate.

I can't figure it out. Is there a better way that I
should be using? Please help, I'm at a dead end.

Thanks,

LadyAmethyst

Hi again

I suggest you pass the name of the calling form to frmDeposit via the
OpenArgs property:

DoCmd.OpenForm strDocName, , , strLinkCriteria, OpenArgs:=Me.Name

Then, in Form_Load:

Dim frmCallingForm as Form
If Not IsNull(Me.OpenArgs) then
Set frmCallingForm = Forms(Me.OpenArgs)

You can then refer to controls on the calling form as, for example:
frmCallingForm![Division]
instead of:
Forms![frmEstimate]![Division]

Where the control names are different, you can check the form name first:
If frmCallingForm.Name = "frmEstimate" Then
txtEstimateTotalCost = frmCallingForm![EstimateTotalCost]
Else
txtEstimateTotalCost = frmCallingForm![RevEstimateTotalCost]
End If

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

 

[Graham Mandeno]

Hi LadyA

You really should post a new question as a new thread. That way it has a
better chance of being looked at by more people.

I can't immediately see any reason why your SQL query is not returning the
required record. I suggest you save it as a query, and then open the query
and see if the record(s) you expect to see are actually in there.

I assume JobID *is* a text field??
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham,

Thanks. Works great. Sorry I didn't get back to you
sooner, but I've been under the weather.

I have another problem. I have 1 form that is supposed
to display data from 2 records (tblCustomer & tblJobSite).
The problem is that on some records the RecordSource
( an SQL query ) works fine and sometimes it dosen't
work at all and gives me a form with no data in the
form fields. (Yes there is data in the tables.)
If I open frmJobSiteInformation by itself (not through
the frmEstimate, then all the records are displayed.
Data Entry on this form is set to No because this is
not the place where data entry or corrections
need to be made. I have both a frmCustomer goes to
tblCustomer and frmJobSite goes to tblJobSite this
is the place to enter the initial information. There is
a CustomerID field in both tblCustomer and tblJobSite.
There is also the JobID field in both tblJobSite and
tblEstimate.

The form is frmJobSiteInformation. the SQL query was
actually built by the forms Wizard.

SELECT DISTINCTROW tblJobSite.CustomerID, tblJobSite.JobID,
tblJobSite.ContactName, tblJobSite.JobSiteAddress,
tblJobSite.JobSiteCity, tblJobSite.JobSiteState,
tblJobSite.JobSitePhone, tblCustomer.CompanyName,
tblCustomer.CustContactName, tblCustomer.CustomerAddress,
tblCustomer.CustomerCity, tblCustomer.CustomerState,
tblCustomer.CustomerZipCode, tblCustomer.CustomerPhone
FROM tblCustomer INNER JOIN tblJobSite ON
tblCustomer.CustomerID = tblJobSite.CustomerID;

Now there is a command button in the frmEstimate
(cmdJobSiteInformation) that opens the frmJobSiteInformation
matching the field JobID in both the frmEstimate and
the frmJobSiteInformation.

Private Sub cmdJobSiteInformation_Click()
On Error GoTo Err_cmdJobSiteInformation_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmJobSiteInformation"

stLinkCriteria = "[JobID]=" & "'" & Me![txtJobID] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdJobSiteInformation_Click:
Exit Sub

Err_cmdJobSiteInformation_Click:
MsgBox Err.Description
Resume Exit_cmdJobSiteInformation_Click

End Sub

The frmJobSiteInformation is displayed to the screen
and it has a command button (cmdPrintEstimate)
after the Estimate is printed the user returns to the
frmEstimate and can exit back to the Switchboard
or enter another estimate.

I can't figure it out. Is there a better way that I
should be using? Please help, I'm at a dead end.

Thanks,

LadyAmethyst

Hi again

I suggest you pass the name of the calling form to frmDeposit via the
OpenArgs property:

DoCmd.OpenForm strDocName, , , strLinkCriteria, OpenArgs:=Me.Name

Then, in Form_Load:

Dim frmCallingForm as Form
If Not IsNull(Me.OpenArgs) then
Set frmCallingForm = Forms(Me.OpenArgs)

You can then refer to controls on the calling form as, for example:
frmCallingForm![Division]
instead of:
Forms![frmEstimate]![Division]

Where the control names are different, you can check the form name first:
If frmCallingForm.Name = "frmEstimate" Then
txtEstimateTotalCost = frmCallingForm![EstimateTotalCost]
Else
txtEstimateTotalCost = frmCallingForm![RevEstimateTotalCost]
End If

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand