Help with VBA Code

[Lavenderchan]

Hi I have a question about the following vba code.
I put a break point in the code the id is returning the value i need but the reserve for replacement id and excess income id is returning null.
What should i do to correct this? an open args?
Any advise would be help ful.
when i try to open the form it asks for a paratmeter value for the id.

here is my code.
Private Sub Assigned_To_AfterUpdate()
If Me.cboTask = ("Reserve for Replacement") Then
DoCmd.OpenForm "Reserve for Replacement Details", , , "ID='" & Me.Reserve_for_Replacement_ID & "'"
End If

If Me.cboTask = ("Excess Income Report") Then
DoCmd.OpenForm "Excess Income Details", , , "ID='" & Me.Excess_Income_ID & "'"

End If

End Sub
Thanks,
Keri

 

[dhookom]

You don't have any reference to the value of ID. If the other IDs are null then make sure the user has entered these values.

If this is for a new record, you might want to try save the record prior to opening another form. You can use the line:

   Me.Dirty = False

Duane
Hook'D on Access
MS Access MVP

 

[PWise]

try cstr("ID=" & Me.Reserve_for_Replacement_ID)

 

[GregTheGeek]

Keri,

Typically, when Access prompts for a value, it means it doesn't recognize one of the expressions. This happens a lot in queries when you make a type-o in one of the criteria expressions, or specify a filed that it cannot find.

If you need further help, I will need more detail. What code fires when you open the Excess Income Details and Reserve for Replacement Details forms?

GTG

 

[Lavenderchan]

Thank you for your response how do I write the cstr("ID=" & Me.Reserve_for_Replacement_ID) in the code?

 

[Lavenderchan]

Hi Greg

The open form fires when the code runs. A paratmeter value is propting for the id so I think that might be were my problem is.

 

[dhookom]

Do you have ID fields in the record sources of both forms you are opening?

Duane
Hook'D on Access
MS Access MVP

 

[Lavenderchan]

The id fileds in the record soucre for the two subforms is Excess Income ID and Reserve for Replacement ID. ID is on the form.

 

[dhookom]

Perhaps you have reversed the fields. I can't see the record sources of your forms or the field data types.
Maybe:

Private Sub Assigned_To_AfterUpdate()
    If Me.cboTask = ("Reserve for Replacement") Then
        DoCmd.OpenForm "Reserve for Replacement Details", , , _
            "Reserve_for_Replacement_ID='" & Me.ID & "'"
    End If

    If Me.cboTask = ("Excess Income Report") Then
        DoCmd.OpenForm "Excess Income Details", , , _
            "Excess_Income_ID='" & Me.ID & "'"
    End If

End Sub

Duane
Hook'D on Access
MS Access MVP

 

[Lavenderchan]

hi I reveresed it I inserted a break point the reserve for replacement id and excess income id is returning a null value. How do I get this to work? do i try an open args?

Thanks,
Keri

 

[Lavenderchan]

I've tried the open args and the reversing the code and I'm still getting the paratmenter of id showing up. I'm an confused to why this is happening.

 

[dhookom]

Hint: [red]I can't see the record sources of your forms or the field data types.[/red]

I'm not sure how we can troubleshoot when we can't see this information.


Duane
Hook'D on Access
MS Access MVP

 

[Lavenderchan]

Here are the data types. I used the sql view of the queries for the forms.The idand reserve for replacement id and excess income id are primary keys.
here is the main form
SELECT Tasks.[Reserve for Replacement ID], Tasks.[Excess Income ID], Tasks.ID, Tasks.[Property Name], Tasks.[REMS Number], Tasks.[FHA Number], Tasks.[Contract Number], Tasks.[Project Manager], Tasks.Task, Tasks.[Open Date], Tasks.[Open By], Tasks.[Assigned To], Tasks.Status, Tasks.[% Complete], Tasks.[Due Date], Tasks.[Time Frame], Tasks.[Closed Date], Tasks.Comments, Tasks.Archive
FROM Tasks;
Here are the subforms
SELECT [Reserve for Replacement].[Reserve for Replacement ID], [Reserve for Replacement].[Property Name], [Reserve for Replacement].[REMS Number], [Reserve for Replacement].[FHA Number], [Reserve for Replacement].[Project Manager], [Reserve for Replacement].[Open Date], [Reserve for Replacement].[Project Address], [Reserve for Replacement].[Project Phone], [Reserve for Replacement].[Project City, State, Zip Code], [Reserve for Replacement].Mortgagee, [Reserve for Replacement].[Mortgagee Contact], [Reserve for Replacement].[Mortgagee Address], [Reserve for Replacement].[Mortgagee City, State, Zip Code], [Reserve for Replacement].[Mortgagee Contact Information], [Reserve for Replacement].[Mortgagor/Management Agent], [Reserve for Replacement].[Mortgagor Contact Name], [Reserve for Replacement].[Mortgagor Address], [Reserve for Replacement].[Mortgagor City, State, Zip Code], [Reserve for Replacement].[Mortgagor/Management Agent Contact Information], [Reserve for Replacement].[Reserve for Replacement], [Reserve for Replacement].[Residual Receipt], [Reserve for Replacement].[Current Monthly Deposit], [Reserve for Replacement].[Account Cash Balance], [Reserve for Replacement].Other, [Reserve for Replacement].Total, [Reserve for Replacement].[Confirmed Beginning Balance], [Reserve for Replacement].[Amount Requested], [Reserve for Replacement].[Amount Released], [Reserve for Replacement].[New Balance], [Reserve for Replacement].[Number of Units], [Reserve for Replacement].[Mimmum Balance Requried], [Reserve for Replacement].[24 Months of Deposits at $500 per Unit], [Reserve for Replacement].[24 Months of Deposits at $1000 per Unit], [Reserve for Replacement].Comments, [Reserve for Replacement].Archive
FROM [Reserve for Replacement];
the second sub form
SELECT [Excess Income Current].[Excess Income ID], [Excess Income Current].[Property Name], [Excess Income Current].[REMS Number], [Excess Income Current].[FHA Number], [Excess Income Current].[Project Manager], [Excess Income Current].[Missing Report], [Excess Income Current].[Report Period], [Excess Income Current].[Date Received], [Excess Income Current].[Income Earned], [Excess Income Current].[Income Retained], [Excess Income Current].[Income Owed], [Excess Income Current].[Paid to HUD], [Excess Income Current].[Check Number]
FROM [Excess Income Current];

 

[dhookom]

You don't have any fields with the name "Reserve_for_Replacement_ID". You also didn't provide the field types. What are the relationships between the tables? It doesn't look like the ID field from Tasks is related to any other tables so it should not be used in the WHERE CONDITION of any DoCmd.OpenForm.

Duane
Hook'D on Access
MS Access MVP