View data in a form for information only

[Sugada]

I have a form called BudgetData, with a combo box called
category and another one called ExpenseType. Category
populates from another table (BudgetHistory) using a
distinct query in the row source. ExpenseType is
populated from whatever was selected from Category and then a Me.ExpenseType.Requery

Okay, fine, This works great!

Now I would like information to be display from the
BudgetHistory table (i.e. Amount2001, Amount2000) into this form BudgetData based on what was just select from both combo boxes Category and ExpenseType.

I do not want this bound to the records being created by this form. This is for display information only.

Thank you, Sugada
.

 

[RickSpr]

Since the fields aren't bound, you'll have to fill in the data yourself, using code, probably in the second combo box's AfterUpdate procedure.

    Dim db As Database, rst As Recordset

    Set db = CurrentDB()
    Set rst = OpenRecordset("BudgetHistory") ' or a query
    rst.FindFirst &quot;<criteria based on combo box value>&quot;
    If Not rst.NoMatch Then
        Me![control1] = rst![field1]
        Me![control2] = rst![control2]
    End If
    rst.Close
    Set rst = Nothing
    Set db = Nothing

Rick Sprague

 

[Sugada]

Thanks Rick for your response to this thread.

Unfortionitly I'm having problems getting this to work. I'm assumming some of my field names needed to replace some code in your code. I just can't figure out what that is suppose to be.

Please view this picture from my web site for the error I'm getting.

http://www.sugada.com/AccessHelpPic.htm

Thanks, John

 

[RickSpr]

Oops! Typo on my part.

That should be "db.OpenRecordset(...".

The names to replace are [control1] and [field1], etc. Rick Sprague

 

[Sugada]

Cool, Yeah, Okay, I'm stupid.

I posted my mdb on my web site for download. I'm sure it is clear to what I'm doing wrong. I just don't see the problem. H E L P

http://www.sugada.com/Tek-TipsProblem.htm

(Access 2000)

Thanks, Sugada <--- Stupid Access Guy!!!

 

[RickSpr]

Oh man! I wrong a long, long response explaining what you'd done wrong and what I'd done wrong, with great background info, etc. but when I posted it Tek-Tips server messed up and I just lost it all. I don't have time to recreate it all over again, so I'm afraid I'm just going to have to drop the code on you and hope you'll research what makes it work. Sorry, pal!

Private Sub ExpenseType_AfterUpdate()
    Dim db As Database
    Dim rst As Recordset

    Set db = CurrentDb()
    Set rst = db.OpenRecordset("ITBudgetHistory", dbOpenDynaset)
    rst.FindFirst "Category = '" & Me!Catagory _
        & "' AND ExpenseType = '" & Me!ExpenseType & "'"
    If Not rst.NoMatch Then
        Me![Actual1999] = rst![1999Actual]
        Me![Actual2000] = rst![2000Actual]
    End If
    rst.Close
    Set rst = Nothing
    Set db = Nothing
End Sub

Rick Sprague

 

[Sugada]

Thanks Again Rick. I'm sorry you can't retype it. I'm still having issues and I know this code you wrote goes in the event procedures, but what about my text box Actual1999 and or Actual2000 I'm not sure what to do here. If this is unbound then what goes in my control source?

I'm sure this whole thing will make sense when it works but it doesn't.

My web site is down and I would have hoped you could have downloaded it, that way when you dropped the code in you could see what blew up.

I can email it to you? JSeymour@acloche.com

If not, thanks for all your help. John

 

[RickSpr]

You had said you wanted Amount2000 and Amount2001 displayed, but the database you put on your web site, and that I downloaded, had so such fields, so I used the 1999Actual and 2000Actual fields instead.

I added Actual1999 and Actual2000 text boxes to your form to display these values. (I could have used the field names for the controls, but using different names makes the code easier to understand.) These text boxes are unbound, which means their Control Source properties are blank. They should also be disabled (Enabled = No) and locked (Locked = Yes) so the cursor won't stop on them.

I then set the After Update property of ExpenseType to &quot;[Event Procedure]&quot;, and added the code I gave in my previous post to the form's module. I tested it, so I know it works.

Your web site seems to be fine right now. If you're still having problems, could you upload it again? I'd rather not expose my own email address. Rick Sprague