Solved

Delphi - Dbl Click row from ADO Query result grid - open another form / grid with that record selected.

Posted on 2008-10-07
16
2,303 Views
Last Modified: 2012-06-27
Hi Experts
Delphi app using Access ADO tables.
I currently have two forms to check for duplicate redords. The main duplicates form has a grid showing all record details and a button which opens a sub form to run two ADOqueries (via buttons) to check for duplicate Last Names, and the other, duplicate addresses, results shown in a grid.
Because the sub form grid has less detail for checking purposes (and because I don't know how to locate or find a pointer to a given record in a query to delete it) the sub form has to be closed in order to search for and delete any duplicates found. This is laborius.
Can any one help me with the code to double click on a record in the sub form grid (it's a query result) so that it closes the sub form, and returns to the main duplicates form (still open in the background) but with the sub form 'double clicked' record selected?
There is an 'ID' field (record number) that could be used for identification...?
As I'm only a novice at Delphi - I'd appreciate your indulgence by showing me the code and explaining where it should fit in with the above.
Additional Info: On the main form the DBGrid1 has data source1 linked to DataModule1.Contacts, and on the sub form another data source1 linked to ADOQuery1.
Many thanks,
A3dvm
Heres the sub form code:
 procedure TFormDupLastName.FormCreate(Sender: TObject);
begin
    DBGrid1.Columns.LoadFromFile('C:\No25Data\Text\Grid_DupLastName_Width.txt') ;
     LabPressBtn.Visible:=True;
end;
      //Save grid data
procedure TFormDupLastName.FormClose(Sender: TObject; var Action: TCloseAction);
begin
       DBGrid1.Columns.SaveToFile('C:\No25Data\Text\Grid_DupLastName_Width.txt') ;
  end;
      // Close form button  = focus EdSearch
procedure TFormDupLastName.sBitBtn2Click(Sender: TObject);
begin
     ADOQuery1.Close;
     ADOQuery1.SQL.Clear;
     Close;
     FormDuplicatesCheck.EdSearch.SetFocus;
     LabPressBtn.Visible:=True;
     LabDupAdd.Visible:=False;
     LabDupName.Visible:=False;
end;
       // Dup Last Name
procedure TFormDupLastName.DupLastNameBtnClick(Sender: TObject);
begin
      ADOQuery1.Close;
      ADOQuery1.SQL.Clear;
      ADOQuery1.SQL.Add ('select distinct a.ID as [ID], a.Name_First as [First Name], a.name_last as [Last Name], a.Add_Hsename_nbr as [Hse Number], a.add_Street as [Street], a.add_district as [District] from contacts as A, contacts as B');
      ADOQuery1.SQL.Add ('where A.name_first=B.name_first and A.name_last=B.name_last and A.id<>B.id order by a.name_last, a.name_first, a.id');
      ADOQuery1.Parameters.Clear;
      ADOQuery1.Open;
      DBGrid1.Columns.LoadFromFile('C:\No25Data\Text\Grid_DupLastName_Width.txt') ;
      LabPressBtn.Visible:=False;
      LabDupName.Visible:=true;
      LabDupAdd.Visible:=False;
      end;
           //Dup Address button
procedure TFormDupLastName.DupAddBtnClick(Sender: TObject);
begin
      ADOQuery1.Close;
      ADOQuery1.SQL.Clear;
      ADOQuery1.SQL.Add ('select distinct a.ID as [ID], a.Name_First as [First Name], a.name_last as [Last Name], a.Add_Hsename_nbr as [Hse Number], a.add_Street as [Street], a.add_district as [District] from contacts as A, contacts as B');
      ADOQuery1.SQL.Add (' where A.add_street=B.add_street and A.id<>B.id order by a.add_street, a.name_last, a.name_first, a.id');
      ADOQuery1.Parameters.Clear;
      ADOQuery1.Open;
      DBGrid1.Columns.LoadFromFile('C:\No25Data\Text\Grid_DupLastName_Width.txt') ;
      LabPressBtn.Visible:=False;
      LabDupAdd.Visible:=true;
      LabDupName.Visible:=False;
end;
end.
 
Main Duplicates form code  (I've left out the search routine that begins it...)
 
procedure TFormDuplicatesCheck.sBitBtn1Click(Sender: TObject);
begin
    if DataModule1.dtContacts.State in[dsBrowse, dsInsert] then
    begin
    if sMessageDlg ('Are you really sure you want to'+#13+#10'permanently delete this contact?', mtConfirmation,
    [mbYes, mbNo], 0) = mrYes then
    DataModule1.dtContacts.Delete ;
    edSearch.text:='' ;
    end
    else
    DataModule1.dtContacts.Cancel
    end;
procedure TFormDuplicatesCheck.sBitBtn2Click(Sender: TObject);
begin
      close;
end;
procedure TFormDuplicatesCheck.DupLastNameBtnClick(Sender: TObject);
begin
     FormDupLastName.ShowModal;
end;
procedure TFormDuplicatesCheck.FormClose(Sender: TObject;
  var Action: TCloseAction);
begin
       DataModule1.dtContacts.Refresh;
end;
end.

Open in new window

0
Comment
Question by:a3dvm
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 8
16 Comments
 
LVL 13

Expert Comment

by:rfwoolf
ID: 22661862
To locate a record in the resultset of a dataset you use the oddly named "locate" method (sarcasm alert :D)
example:
If MyADOQuery1.Locate('ID', MyADOQuery2.fieldbyname('ID').value, []) then
begin
  showmessage('If you are reading this then the record was found and is about to be BALATED!!!');
  MyADOQuery1.Delete;
end
else
begin
  showmessage('If you are reading this then the record was NOT found');
end;
To close the "Sub" form couldn't you do something like
  MySubForm.Close;
or if necessary
 MySubForm.ModalResult := 1; //if the form is a modal form, although, just .close should still work
0
 

Author Comment

by:a3dvm
ID: 22662743
Hi woolf
Thanks for the response. Sarcasm accepted. But I did actually find similar code to your ADO1Query.Locate lines before appealing for help, and even managed to produce a similar message dialog!
However, that didn't help me to transfer that record ID information through to the grid in the main form behind, so that it selected the correct record for me to delete.
I suspect this is basic stuff that you think I should know, but like I said - I'm a stumbling novice who's only got this far because of diligent use of Google and Experts Exchange. (you've helped me before...)

So-o-o, how do I (in code) get the result of " MyADOQuery1.Locate('ID', MyADOQuery2.fieldbyname('ID').value, []) " in the sub form, to the grid of the main form (that is open behind) and make it highlight / select the correct record? It's really this bit that stumps me. (actually it all stumps me....)
I presume the "MyADOQuery1.Locate('ID', MyADOQuery2.fieldbyname('ID').value, []) " would go behind a Grid OnDblClick event (in the sub form) - what code and event do I use to "insert" the correct record info in to the main form grid, which uses a different datasource?
I understand how to close the form in between the 2 events above, and can handle the deletion. (Wow!)
Thanks,
a3dvm
0
 
LVL 13

Expert Comment

by:rfwoolf
ID: 22662817
I'll be honest I haven't gone into your attached code, but I have read your question properly and as far as I can understand you never wanted to 'insert' the record of your SUB form into the dataset in your MAIN form -- instead you wanted to LOCATE to it - in other words get the dataset to move its cursor to that record. Then of course your DBGrid on the Main Form will display the located record.
Here's the code again, explained a bit further:
MyADOQuery1.Locate('ID', MyADOQuery2.fieldbyname('ID').value, [])  
>>MyADOQuery1 would be on the MAIN Form (or more accurately, it would be the ADOQuery which your Main Form is pointing to -- the ADOQuery could be stored on any form really)
>>MyADOQuery2 would be the ADOQuery that your SUB form uses.
>>The query would be in a button for example on the SUB Form. So the user would click on a record in the SUB Form, then click the button, and this could would tell your MAIN form to navigate to the record whose ID matches the record in the SUB form.
What else are you missing?
 
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 13

Expert Comment

by:rfwoolf
ID: 22662839
Woops some errors there...

When I said
>>The query would be in a button...
I meant
>>That locate procedure would be in a button's OnClick event..
0
 

Author Comment

by:a3dvm
ID: 22663518
Hi woolf
First, I've just re-read your first message again - slowly - and seen the 'MyQuery1.delete' which I must have skipped over earlier. I can see that would have deleted the record from the sub form.
However, ideally I do want to return to the main form to delete - and yes, my terminology may have been incorrect - I do want to 'locate' the record in the main form as you say.

I've remamed the sub form query to 'ADOQuery2' to keep in step with your instructions.
I've added 'ADOQuery1' to the main form.
Added a button to the sub form and put behind it this code:
procedure TFormDupLastName.BtnRetToDupChkClick(Sender: TObject);
begin
      FormDuplicatesCheck.ADOQuery1.Locate('ID', ADOQuery2.fieldbyname('ID').value, []) ;
      FormDuplicatesCheck.Close;
end;
However, it falls over with a "ADOQuery1 - Cannot perform this operation on a closed dataset".
I've checked the ADO Connection, the DataSource, and the Query - and all the attibutes are the same as the ADOQuery2 which works. The only difference is the code around ADOQuery2.
Am I missing something stupidly simple, like an open, close, clear on ADOQuery1 ??
a3dvm
0
 
LVL 13

Expert Comment

by:rfwoolf
ID: 22663618
Just a small point - you said in your question that the main form's DBGrid's datasource is linked to "DataModule1.Contacts" -- so that's the dataset you want to focus on ("Contacts").
The error "Cannot perform this operation on a closed dataset" means that ADOQuery1 is not 'open' - it isn't active, it holds no records, and you won't be able to move its cursor to a record by using the locate method.
Shouldn'y you rather be trying to locate the contact you want to delete in your Contacts dataset and deleting it from there?
In any case if you are sure its ADOQuery 1 that you want to talk to, what you can do for debugging purposes is drop a DBGrid onto (any) form and connect it to ADOQuery1 via a datasource, and then just before and after your Locate method do a showmessage('1') and showmessage('2') so that at the very point of the error you can see what's happening in your dataset.
Hope I haven't  confused you.
0
 
LVL 13

Expert Comment

by:rfwoolf
ID: 22663643
I'm not sure what you're doing with ADOQuery1 - or what its SQL is - judging from your error it's not doing anything :p
0
 

Author Comment

by:a3dvm
ID: 22664270
Hi again,
Sorry for being thick, I'm confused - I created ADOQuery1 as from your text I thought you intended me to create a new query (ADOQuery1) on the main form and use the parameter
('ID', MyADOQuery2.fieldbyname('ID').value, [])  in it's code.
So lets forget ADOQuery1, I'll delete it for now....
I have in place on the sub form ADOQuery2 which returns duplicate records from 'Contacts' from a button click. (code as attached earlier)
If I select / highlight a record in the sub form grid - how do I get the main form grid to select the same record? At present there is no query on the main form.
I'm sure this is obvious to gifted guys like yourself, but I'm a long way off being that!
A3dvm
0
 

Author Comment

by:a3dvm
ID: 22664299
...how do I get the main form grid to 'select' the same record....
> Imaybe I meant 'locate' - i.e. go to the row.....
0
 
LVL 13

Accepted Solution

by:
rfwoolf earned 125 total points
ID: 22664346
Okay keep in mind that your DBGrid is only a data-aware control, and typically you control the actual DATASET to refer to records and manipulate them. Your dataset is your TADOQuery (others are TADOTable - or there are others like TTable and TSQLQuery and TClientDataset and so forth).
So, when your user clicks on the DBGrid on your SUBForm, you can retrieve the ID of the record they've selected by referring to that DBGrid's dataset. Usually you will know what it is, but you could refer to it dynamically like this:
MyDBGrid.DataSource.DataSet

Getting back to your question...
You want to take this ID from the SUBForm and look it up in the DATASET of the DBGrid on your Main Form, right? Then you would tell THAT Dataset to locate.
Here's the code dynamically:
MyMainFormDBGrid.DataSource.Dataset.Locate('ID', MySubFormDBGrid.DataSource.Dataset.FieldByName('ID').value, []);
(Replace "MyMainFormDBGrid" with the name of the DBGrid on your main form, replace "MySubFormDBGrid" with the name of the DBGrid on your SubForm, replace 'ID' with the NAME of the field that you want to use an identifier (which you say is 'ID'). And of course you may have to PROceed those DBGrid names with their relevant forms, so instead of MyMainFormDBGrid, it would by MyMainForm.MyMainFormDBGrid).
Does that help ?
0
 

Author Comment

by:a3dvm
ID: 22664406
I'm sure it will - I'm getting on to it now.
Thanks for the explanation, I 'm sure it will help me understand better.
I'll let you know as soon as I've tried it.
0
 
LVL 13

Expert Comment

by:rfwoolf
ID: 22664421
Just to make this clear to you ...
Your DBGrid has a Datasource property which will point to a TDatasource somewhere in your application. That Datasource has a DataSet property, which will refer to a TADOQuery or TADOTable somewhere in your application.
If you want to tell your DBGrid to go from its current record to the next record, you actually talk to its Dataset, not to the DBGrid. You say MyADOTable.Next, where MyADOTable is the TADOTable that is connected to the DBgrid via the TDataSource.
Of course the DBGrid does allow you to edit records and even to navigate amongst records, by as a developer you would never control your data through the DBGrid - you would always talk to its dataset.
The "cursor" in the DBGrid that points to a particular record is the same cursor that your Dataset is on. So if you click the last record in your DBgrid, then your Dataset will navigate to the last record. When we say ADOTable1.FieldByName('FieldName').value it refers to the record that the cursor is currently on.
Databases are one of the biggest learning curves a Delphi (or any) developer needs to make. I remember when I was learning it took me a long time to get it right, in fact I had to read through all of Borland's helpfiles - but these days I would recommend just doing the tutorial on About.com -- of course there's so much more to learn and (as I've learnt these past few days when I changed from TTables to TIBTables and from a Paradox database to Firebird) there's still a lot that I still a bit that I have to learn.
0
 

Author Comment

by:a3dvm
ID: 22664521
Excellent! You're a star.
This was the final code that did the trick:
procedure TFormDupLastName.BtnDupDeleteClick(Sender: TObject);
begin
       FormDuplicatesCheck.DBGrid1.DataSource.Dataset.Locate('ID', FormDupLastName.DBGrid1.DataSource.Dataset.FieldByName('ID').value, []);
       FormDupLastName.Close;
end;
Thanks for the additional explanation - it will help in my quest.......
I'm perfectly happy with what I have now - but I'm intriqued to know whether that same code would work from a double click on the record as well as a button. (if put behind a grid OnDblClick event?)
Many thanks,
a3dvm
0
 

Author Closing Comment

by:a3dvm
ID: 31503938
Very patient and helpful to a novice.
0
 
LVL 13

Expert Comment

by:rfwoolf
ID: 22664596
Yes a OnDblClick event would also work
 By the way you could slightly improve your code if you like to take into account what happens if the record cant be located (isn't found) on the Main Form -- the locate method returns a boolean result, so you could call the locate method with an IF:
 

IF FormDuplicatesCheck.DBGrid1.DataSource.Dataset.Locate('ID', FormDupLastName.DBGrid1.DataSource.Dataset.FieldByName('ID').value, []) = TRUE then
 FormDupLastName.Close
ELSE
showmessage('Record not found');

Open in new window

0
 

Author Comment

by:a3dvm
ID: 22664632
Great. I'll do that, thanks.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The uses clause is one of those things that just tends to grow and grow. Most of the time this is in the main form, as it's from this form that all others are called. If you have a big application (including many forms), the uses clause in the in…
Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses
Course of the Month7 days, 21 hours left to enroll

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question