UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> 3 Tables Unique Key Problem In The Form?, Access 2016    
 
   
cgmark
post Apr 4 2019, 08:37 AM
Post#1



Posts: 7
Joined: 20-July 18



Hello,
I've got 3 Tables:
1-Costumers
2-Companies
3-Invoice

The table 2 got a link to table 1, in the field LR (LegalRepr. of the Company) because you have to choose the LR from the Customers Table.

The Table3 (Invoice) has 2 fields: Customer and Company, meaning you can either choose Customer from the Table 1 or Company from Table 2.
Anyway the Form gives me error (doesnt load record) due to the relationship of LR-Customer
In fact, if I remove the field Company from Table3, it works normally.

I still want to be able to choose Customer or Company (it's ok if you choose Company you don't have to choose Customer and viceversa)
but I don't know how to do it or to fix the Form.
Go to the top of the page
 
GroverParkGeorge
post Apr 4 2019, 09:07 AM
Post#2


UA Admin
Posts: 35,129
Joined: 20-June 02
From: Newcastle, WA


Data entry is almost always best handled with a form bound to a single table. One of the primary reasons for that is exactly the problem you have here.

In this case, your subselection for LR should be a dropdown, or combo box with the two options. That doesn't require either the Customers or Companies table to be included in the form's recordsource.


--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
cgmark
post Apr 4 2019, 09:41 AM
Post#3



Posts: 7
Joined: 20-July 18



Yes it is like you said,
but I included the tables in the recordsource to get other fields (only in the form like the field: Address, etc.) and then print them in a file.
Go to the top of the page
 
GroverParkGeorge
post Apr 4 2019, 09:45 AM
Post#4


UA Admin
Posts: 35,129
Joined: 20-June 02
From: Newcastle, WA


I see. Perhaps you need two recordsources. One for the form and the second for the report.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
cgmark
post Apr 4 2019, 09:53 AM
Post#5



Posts: 7
Joined: 20-July 18



Thanks to your advices I'm figuring it out with a combox (still in progress!) thanks.gif

I'll see if it works!
Go to the top of the page
 
GroverParkGeorge
post Apr 4 2019, 09:56 AM
Post#6


UA Admin
Posts: 35,129
Joined: 20-June 02
From: Newcastle, WA


Please keep us updated on your project.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
cgmark
post Apr 4 2019, 12:31 PM
Post#7



Posts: 7
Joined: 20-July 18



I managed something, so the first record is ok (shows correct data in the combobox linked to the customer), but when I go next record or new record, the combobox keeps showing the first record data.
I tried with some Requery commands (on update of the Mask or of the field Customer) but didnt work
Go to the top of the page
 
GroverParkGeorge
post Apr 4 2019, 12:40 PM
Post#8


UA Admin
Posts: 35,129
Joined: 20-June 02
From: Newcastle, WA


We would probably be able to offer more specific advice if you can either post the VBA, or better, upload a sample copy of the accdb with just enough test data to see how it should work. Compact and Repair the sample, compress it into a ZIP file and upload it as an attachment. Thanks.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
cgmark
post Apr 8 2019, 11:10 AM
Post#9



Posts: 7
Joined: 20-July 18



Sorry I've been busy these days!
I solved the error, like you said, just by using the 3rd table as recordset and by calling customers and companies from a combobox.

Now the problem is how to select the item in the combobox automatically when I change record in the mask (AfterUpdate, and also when I'm moving next or previous record).

Because if I go on next record or previous one, the selected item remain the same.
Go to the top of the page
 
mike60smart
post Apr 8 2019, 02:11 PM
Post#10


UtterAccess VIP
Posts: 13,183
Joined: 6-June 05
From: Dunbar,Scotland


Hi

The only way we will be able to give any good answer to your problem is if you upload a zipped copy of the database.


--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
tina t
post Apr 8 2019, 04:35 PM
Post#11



Posts: 5,955
Joined: 11-November 10
From: SoCal, USA


QUOTE
Now the problem is how to select the item in the combobox automatically when I change record in the mask (AfterUpdate, and also when I'm moving next or previous record).

Because if I go on next record or previous one, the selected item remain the same.

sounds like the combobox control is unbound. open the form in Design view, click on the combobox control to select it. then look at the properties list, at the ControlSource property (not the RowSource property), and tell us what it shows.

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
cgmark
post Apr 10 2019, 09:12 AM
Post#12



Posts: 7
Joined: 20-July 18



It's Empty.

Anyway I managed the problem like this:

First, used as recordset of the mask only the 3rd table (which contained the other 2 tables link).
Second, the combobox draws fields (like address, email etc.) by matching IDs from the link.
Third, there was a problem of update/refresh, but I solved with a "SaveRecord" command after choosing from the dropdown of Customers (or Companies).
Fourth, I have to do a Requery after moving to Next (or Previous) record in the mask (otherwise the selected item of the combobox was the first picked in the first record)

How do I empty the db from data, and upload only a part of it?
Go to the top of the page
 
tina t
post Apr 10 2019, 04:13 PM
Post#13



Posts: 5,955
Joined: 11-November 10
From: SoCal, USA


QUOTE
sounds like the combobox control is unbound. open the form in Design view, click on the combobox control to select it. then look at the properties list, at the ControlSource property (not the RowSource property), and tell us what it shows.

QUOTE
It's Empty.

okay, then, that's not a "problem", per se. it's expected behavior. if a combobox control is not bound to a field in the form's RecordSource, then it has nothing to "show" for each record. it only has the last value that was selected from the droplist - which is not being stored anywhere.

bind your combobox control to a field in the form's RecordSource - whatever field that is supposed to be storing that value. you do that by putting the fieldname in the control's ControlSource property. once you do that, then whatever value you choose from the combobox droplist should be stored in the Current record, and when you move from one completed record to another, the combobox value should show what is stored in the field of the record you're currently on.

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    17th June 2019 - 01:51 PM