Changing a field's value based on another field. (1 Viewer)

[emus]

This database is for a high school project so it won't actually be used. As a result, I know close to nothing about coding and developed and designed this database from the ground up, so don't crucify me for bad database design.

So, the database records transactions of a certain company.

The transactions are recorded as purchase or sale, the purchase and sale values are pulled from another table.

In the "Sellers" and "Buyers" tables I entered the details of said "company" as SellerID and BuyerID 1, so if a transaction is a purchase, the BuyerID of the transaction must be 1, and vice versa. I want to make it automatic with a macro or module, I'm not sure how to do it.

The best solution would be something like "If 'Purchase/Sale' is 'Purchase', then "[Transactions].[BuyerID] must be 1" and the same thing for Sale. If this is not viable, "If [Transactions].[SellerID]<>1, Then [Transactions].[BuyerID] = 1" or something to that effect. The problem is, I don't know how to write this in code or macros. Thanks for your help.

 

[theDBguy]

Hi. I'm not sure I understand your question. Was there any guidance given for your school project? If you can share it, we might be able advise if your table design is appropriate.

Otherwise, you can try to use the AfterUpdate event of the form control to trigger the code to assign the correct value to your other field.

 

[Gasman]

Really should just have one table for Buyers and Sellers ?
Could a Buyer also be a Seller?

 

[emus]

Hi. I'm not sure I understand your question. Was there any guidance given for your school project? If you can share it, we might be able advise if your table design is appropriate.

Otherwise, you can try to use the AfterUpdate event of the form control to trigger the code to assign the correct value to your other field.

It isn't a normal school project, it is an IB program project and there are no directions or formats to follow. I don't think the table design should be changed, because I everything works perfectly with the current design and my teacher liked everything I have done so far.

I will describe the problem more clearly. The transactions table records if the transaction is a purchase or sale, and the "Seller" and "Buyer" partaking in the transaction. The company called "ZS Otomotiv" buys car parts from abroad and sells in my country, and the transactions are the companies transactions. When the company is buying parts, the transaction is a "Purchase" and when it is selling parts the transaction is a "Sale".

There are two tables, "Sellers" and "Buyers". "ZS Otomotiv" is recorded in those tables like this. When the transaction is a "Purchase", I want the BuyerID to be 1 in the transactions table and vice versa.

I hope I made it more clear. As for AfterUpdate, I will look into it but I would appreciate it if you could help. Thanks in advance.

 

[June7]

I agree that buyers and sellers should be one table - Companies.

Perhaps use AfterUpdate event of Purchase/Sale combobox.

If Me.cbxPS = "Purchase" Then
     Me!BuyerID = 1
     Me.cbxBuyer.Enabled = False
     Me.cbxSeller.Enabled = True
Else
     Me!SellerID = 1
     Me.cbxSeller.Enabled = False
     Me.cbxBuyer.Enabled = True
End If

Actually, use Conditional Formatting instead of VBA to enable/disable buyer and seller comboboxes.

 

[emus]

I agree that buyers and sellers should be one table - Companies.

Perhaps use AfterUpdate event of Purchase/Sale combobox.

If Me.cbxPS = "Purchase" Then
     Me!BuyerID = 1
     Me.cbxBuyer.Enabled = False
     Me.cbxSeller.Enabled = True
Else
     Me!SellerID = 1
     Me.cbxSeller.Enabled = False
     Me.cbxBuyer.Enabled = True
End If

Actually, use Conditional Formatting instead of VBA to enable/disable buyer and seller comboboxes.

The problem with making buyers and sellers into 1 table is that buyers are only buyer and sellers are only sellers (With the exception of ZS Otomotiv). Would it make a difference or do you think I still should merge them into one table?

Edit: I understand now what you suggest. I should make a table called "Companies" and specify if the company is a buyer or a seller in the table. If I did that would the code you wrote still work?

 

[June7]

Yes, the code should still work. But again, Conditional Formatting would be better for setting comboboxes enabled/disabled.

Are you sure a company will always be only a buyer or seller?

 

[emus]

Yes, the code should still work. But again, Conditional Formatting would be better for setting comboboxes enabled/disabled.

Are you sure a company will always be only a buyer or seller?

Yes, because the company imports parts into the country and sells, they don't sell abroad, so a company will only be a buyer or a seller. One thing is, I don't totally understand what you mean by "Conditional Formatting would be better for setting comboboxes enabled/disabled.", and what is the disadvantage of using my current method?

 

[The_Doc_Man]

I'll take a different direction here. Since this is a school project, I am going to walk a narrow line here intentionally. Giving you a direct answer is not in the spirit of the forum when it comes to school projects. But giving hints is O.K.

You said you weren't sure how to do this. I'm not surprised, as Access is VERY different from the other Office utilities. For Access, the solution will start by deciding WHEN to do it and with which mechanism. Access requires that things be driven by EVENTS.

If you are using a form, you can select form-level events or control-level events. In either case, the available events are listed in design view if you show the properties and select the Events tab. These events are segregated according to the object to which they apply. If you select the form, you see the form's events. If you are using controls on the form and select one of the controls, you will see the control's events. The key to solving the problem through forms will be to learn how and when the events occur, and that will require you to look up the specific event. As a quick example, if you use the great Google brain to look up a Before Update event, you would search for "Access Before Update event" and that would tell you how and when that event works.

IF you pick a form-level or control-level event, the next decision will be whether you want to use VBA code or a macro, either of which is possible. Many times, folks on this forum will steer you away from macros because they are clunky and have limited error management capability. But for a beginning school project, they work adequately. I'm not going to tell you which one to use. I'm just naming things you can research to figure out how to attack the problem. For simple tasks like copying data from point A to point B, the solution could be as simple as a single line of VBA code. For using a Macro, a single macro step might be all you need. It will be harder to look up the help messages than it will to implement in either case. But part of my answer is oriented to getting you used to consulting the online help files.

If you are using a query to accomplish your goal, you still need an event, which in this case would be to launch the query. There, you have the choice of using a SELECT query to just see the new value in the targeted field, or use an UPDATE query to actually modify the targeted field. You can look up the SELECT and UPDATE queries to see how they are used. Which one you choose (if you go via queries) will depend on the exact wording of the problem as stated by your instructor.

 

[June7]

Setting combobox properties by VBA will reflect the same setting for all records. Conditional Formatting will dynamically set the comboboxes as enabled/disabled for each record all at the same time.

 

[Gasman]

Yes, because the company imports parts into the country and sells, they don't sell abroad, so a company will only be a buyer or a seller. One thing is, I don't totally understand what you mean by "Conditional Formatting would be better for setting comboboxes enabled/disabled.", and what is the disadvantage of using my current method?

To my mind, it does not matter if they are one or the other?
Having to go to one table or another table depending on what they are, seems overly complicated to me?

 

[Pat Hartman]

Sorry, you need to change your design. It is not rational in any application to have to hard code something like a companyID. How do you think that would work for Amazon? So the solution is to go back to the data entry form. Use a combo to select the buyer and another to select the seller and store the IDs at that time.

I also would use a single table for buyer and seller. It simplifies the application.

 

[GPGeorge]

One more vote for a more normalized table design. And an uninvited comment on the training some institutions offer. This would not be the first time a teacher in a school purporting to instruct students using Access had a less than firm grasp on the subject matter themselves.

 

[Pat Hartman]

I agree with George. The teacher should have a "solution" and although there will be some flexibility in determining if your project meets the requirements, certain things must exist. Separate tables for Seller and Buyer although not optimal, is not categorically wrong. But, hardcoding a companyID, is categorically wrong.

 

[June7]

Normally, hard coding data would not be appropriate (although I have done it). Since "ZS Otomotiv" must always be on one side of the transaction, it is not necessary to even save this data. Just have field for "Purchase/Sale" options and another field for company transaction is with.

 

[Pat Hartman]

This is a pretty strange application but we don't have the specs so who knows. In a typical OE application you separate Orders from Purchase Orders so the concept of who is a seller and who is a buyer isn't muddled. For an Order, the "company" is always the seller but there is no "seller" ID in the Vendor table for the "company". For a PO, the "company" is always the buyer and there is no "buyer" ID for the "company" in the Customer table.

 

[June7]

Yes, purchases and sales are usually separate tables (http://allenbrowne.com/AppInventory.html) but I can see one Transactions table workable. Now the question comes up are there multiple product items for each transaction so that details table(s) would be needed.