Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2400
  • Last Modified:

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

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
a3dvm
Asked:
a3dvm
  • 8
  • 8
1 Solution
 
rfwoolfCommented:
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
 
a3dvmAuthor Commented:
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
 
rfwoolfCommented:
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
rfwoolfCommented:
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
 
a3dvmAuthor Commented:
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
 
rfwoolfCommented:
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
 
rfwoolfCommented:
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
 
a3dvmAuthor Commented:
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
 
a3dvmAuthor Commented:
...how do I get the main form grid to 'select' the same record....
> Imaybe I meant 'locate' - i.e. go to the row.....
0
 
rfwoolfCommented:
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
 
a3dvmAuthor Commented:
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
 
rfwoolfCommented:
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
 
a3dvmAuthor Commented:
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
 
a3dvmAuthor Commented:
Very patient and helpful to a novice.
0
 
rfwoolfCommented:
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
 
a3dvmAuthor Commented:
Great. I'll do that, thanks.
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 8
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now