Dear,
I have a form that is opened using a sql statement:
Part of the table structure:
-----------------------------------------
TbGeneral:
PK - dsr_id
TbDossierSample:
PK - dsr_sample_id
FK - sample_id
FK - dsr_id
TbCRL
PK - sample_id
----------------------------------------
On the form that is linked to TbGeneral, there is a button that opens
the form linked to TbCRL.
"Dossier" mentioned below is the primary key of TbGeneral (Me!dsr_id)
SQL = "SELECT * FROM TbCRL, TbDossierSample WHERE
(([TbCRL.sample_id]=[TbDossierSample].[sample_id]) AND
([TbDossierSample.dsr_id]=" & Dossier & "))"
Once the form (linked to TbCRL) is opened, records cannot be edited.
I've already read some threads about the issue, explaining the problem
is located at the table structure and saying editable fields are only
possible with a form linked to a single table.
Isn't there any alternative?
(I've tried "Allow.Edits" in VBA without success)
ANY help is most welcome...
Thanks in advance,
CC 5 2128
CC wrote: Dear,
I have a form that is opened using a sql statement:
Part of the table structure: ----------------------------------------- TbGeneral: PK - dsr_id
TbDossierSample: PK - dsr_sample_id FK - sample_id FK - dsr_id
TbCRL PK - sample_id ---------------------------------------- On the form that is linked to TbGeneral, there is a button that opens the form linked to TbCRL. "Dossier" mentioned below is the primary key of TbGeneral (Me!dsr_id)
SQL = "SELECT * FROM TbCRL, TbDossierSample WHERE (([TbCRL.sample_id]=[TbDossierSample].[sample_id]) AND ([TbDossierSample.dsr_id]=" & Dossier & "))"
Perhaps you could enter, just under the SQL line
Debug.Print SQL
Now go to the debug window, copy to clipboard the result, and try to
create a query with it. Does it work?
One reason I question your SQL is that you have
SELECT * FROM TbCRL, TbDossierSample
and I'd think you'd want
SELECT TbCRL.* , TbDossierSample.* ...
Also, verify that you don't have the recordsource type set to snapshot
in the form (under the Data tab for the Form's properties). But I tend
to think you SQL is flawed. Once the form (linked to TbCRL) is opened, records cannot be edited.
I've already read some threads about the issue, explaining the problem is located at the table structure and saying editable fields are only possible with a form linked to a single table.
Isn't there any alternative? (I've tried "Allow.Edits" in VBA without success)
ANY help is most welcome...
Thanks in advance,
CC
"CC" <se********@gmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com... Dear,
I have a form that is opened using a sql statement:
Part of the table structure: ----------------------------------------- TbGeneral: PK - dsr_id
TbDossierSample: PK - dsr_sample_id FK - sample_id FK - dsr_id
TbCRL PK - sample_id ---------------------------------------- On the form that is linked to TbGeneral, there is a button that
opens the form linked to TbCRL. "Dossier" mentioned below is the primary key of TbGeneral
(Me!dsr_id)
CC,
SELECT *
FROM TbCRL
,TbDossierSample
WHERE (([TbCRL.sample_id] = [TbDossierSample].[sample_id])
AND ([TbDossierSample.dsr_id] = " & Dossier & "))"
Ok, it appears you have a cartesian product, where each row of TbCRL
is joined to every row of TbDossierSample (rows * rows), (the WHERE
is only applied after that).
This is, as far as I know, a non-updateable query.
You must specify an SQL-92 standard join (INNER, RIGHT, LEFT) on
primary key values in order for the query to be updateable (in most
cases, anyway).
Sincerely,
Chris O.
one option might be to create the SQL statement in the query builder
and then open the query and then try to add a record to that... if it
won't, then try modifying from there.
Dear Chris,
I haven't had the time yet to come back to the problem (because of
other priorities).
INNER, RIGHT and LEFT JOINS were new concepts to me.
Having read some of the literature, I can now see the problems related
to
a cartesian product.
For the moment I have tested following:
"SELECT TbDossierSample.sample_id, TbCRL.sample_id FROM TbDossierSample
INNER JOIN TbCRL ON TbCRL.sample_id = TbDossierSample.sample_id WHERE
TbDossierSample.dsr_id='" & dossier &"'"
dossier in the above statement is TbGeneral.dsr_id for the record that
is currently displayed.
However now I even get the desired output for the query... Is it
possible to combine multiple INNER JOIN's ?
Best regards,
Seppe
Dear Chris, Dear all,
Executing following SQL statement as a "query object", returns the
desired results...
It also allows for new entries to be added.
SQL = "SELECT TbDossierSample.sample_id, TbDossierSample.dsr_id FROM
TbDossierSample INNER JOIN TbCRL ON TbDossierSample.sample_id =
TbCRL.sample_id WHERE TbDossierSample.dsr_id=" & Dossier & ""
(where "Dossier" is the primary key of TbGeneral, Dossier =
Form_FrmGeneral.Text142)
However, when retrieving the results fails when using lower mentioned
statement.
Forms![fm_CRL_Main].RecordSource = SQL
I've been struggeling with this for some time now!
Thx in advance,
Seppe This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Carl Gilbert |
last post by:
Hi
I have recently been having trouble with the edit items in both the context
menu and edit menu in vs.net designer.
On the context menu, the only items that are not disabled are 'View code'...
|
by: Apple |
last post by:
I had create a query with one table and one query, and then create
form with that query using wizard, but I can't append or edit data in
that form & query. Please help!
|
by: libbythomas |
last post by:
I created an Access 2000 form from a query. But I cannot edit it. I can
flip through the records. If I create a table from the query
(maketable) and then create the form from the table, it is...
|
by: John M |
last post by:
Hello,
In Visual studio .NET 2003,
On datagrid object :
How can I link the button element : edit, to do a some client events (such
as message box,
and do another event just after that to the...
|
by: Aled Hughes |
last post by:
Hi,
I have an MFC application that has a CDialog based dialog box that contains
a .net UserControl (via DDX_ManagedControl) as well as a native edit control
which has the ES_NUMBER style set. Now...
|
by: scenic_man |
last post by:
I'm using Microsoft Visual Basic 2005 Express Edition;
is this the right newsgroup?
I just started within the last two weeks, working my way through a book.
Today, I opened the program, and my...
|
by: nuttynibbles |
last post by:
im doing a edit profile page. the page would pull out data frm mysql and populate the forms respectively.
user will edit and update their profile. The page would then validate the value in the...
|
by: shapper |
last post by:
Hello,
In have a web page layout with various panels.
One panel contains a GridView and other panel contains various
TextBox, DropDownList, etc.
When I click a a GridView row I make visible...
|
by: Adam |
last post by:
Hi All,
I have a grid view which contains an edit button (ie: CommandName="Edit").
When clicked this button displays a form view with the required details
displayed.
Problem: When this...
|
by: barkarlo |
last post by:
I use check box in a form "frmworkorder" like confirmation that's record finish.
How can I write code who will block edit and delete record in form when is check box confirmed.
|
by: Rina0 |
last post by:
Cybersecurity engineering is a specialized field that focuses on the design, development, and implementation of systems, processes, and technologies that protect against cyber threats and...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 2 August 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM)
The start time is equivalent to 19:00 (7PM) in Central...
|
by: linyimin |
last post by:
Spring Startup Analyzer generates an interactive Spring application startup report that lets you understand what contributes to the application startup time and helps to optimize it. Support for...
|
by: erikbower65 |
last post by:
Here's a concise step-by-step guide for manually installing IntelliJ IDEA:
1. Download: Visit the official JetBrains website and download the IntelliJ IDEA Community or Ultimate edition based on...
|
by: DJRhino1175 |
last post by:
When I run this code I get an error, its Run-time error# 424 Object required...This is my first attempt at doing something like this. I test the entire code and it worked until I added this -
If...
|
by: Rina0 |
last post by:
I am looking for a Python code to find the longest common subsequence of two strings. I found this blog post that describes the length of longest common subsequence problem and provides a solution in...
|
by: DJRhino |
last post by:
Private Sub CboDrawingID_BeforeUpdate(Cancel As Integer)
If = 310029923 Or 310030138 Or 310030152 Or 310030346 Or 310030348 Or _
310030356 Or 310030359 Or 310030362 Or...
|
by: lllomh |
last post by:
How does React native implement an English player?
|
by: Mushico |
last post by:
How to calculate date of retirement from date of birth
| |