populating a field with a linked value..

[dniezby]

Boy, I'm just full of lack o'knowledge.



Well, here goes - again.

I have a member table. In that table, I have a member's subscription level. For easy, I'll just do this:

MEMBER_TBL

• member_id (PK)
• memeber_type [Looks up which type of membership a player has]

Now, here is the issue I'm having. In another table I have transactions.

TRANSACTION

• TRANSACTION_ID(pk)
• MEMBER_ID [Look up from the member's table]
• trans_amount
• member_type - HERE IS MY PROBLEM. I would think that this would populate when I enter the member's ID but it doesn't.

I joined the tables and it still doesn't populate. What could I be doing wrong?

[June7]

No, doesn't work that way. Why do you need member type in Transaction? Could a member change type over time and you want to store the type in effect at time of payment? If not, there is no reason to save in Transaction table, the member type is available in the member table.

[dniezby]

I have an access programmer that says, it's SUPPOSED to work that way and YES, I need to store their membership level at the time of the transaction. I don't have access (no pun) to him all the time but I did today and he was scratching his head. Though, he doesn't use 2013 yet.

Later I will have to calculate totals that add and subtract from a member and I don't want all their totals to change when / if their membership level changes. For example, ... Think airline miles. If they were a level 3 member today and purchased $50 in merchandise. Well, if they're level 3 today, they earn 5x the miles. If they cancel, and drop to level 1 and tomorrow they purchase $50..Well today they might only get 2x points. However, they would have still earned the 5x point PLUS these new points. Actually, that's a pretty good description of what I'm trying to do.

[June7]

Saving the member_type value to TRANSACTION table will require code. I use only VBA. Options for retrieving the related info:

1. multi-column combobox to hold the member_ID and member_type info from MEMBER_TBL

2. form RecordSource that joins the two tables, join type "Include all records from TRANSACTION and only those records from MEMBER_TBL that match" then bind textboxe to the member_type field from MEMBER_TBL, set properties Locked Yes and TabStop No

3. DLookup expression in textbox

Then code populates the member_type field from TRANSACTION:
Me!member_type=Me.combobox.Column(1)
or
Me!member_type = Me.textboxname

The real trick is figuring out what event to use. Maybe the AfterUpdate of member_ID combobox.

[dniezby]

How do I do the dlookup?

Okay, I designed this DLookup.

Dim varX As Variant
varX = DLookup("[mem_type]", "members", _
    "[member_ID] = " & Forms!members!member_id)

MsgBox "Dlookup Value is " & mem_type.Value

The [mem_type] is the value I want to obtain from another table.
The "members" is the table I want to pull the data from
The [member_ID] is the value that the two need to be equal to give me the value of mem_type.

Test is that it works. It does show me the correct record for the member and their choice of member ID. I attached it to
Private Sub mem_type_Change()

So, everytime a member changes their membership type I get an updated value.

Thank you for pointing me in the right direction.

I hope it works across forms though. I'm going to test THAT now. My main Member's form uses Tabs to group the information in one form.