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,153 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
  • 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
 
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

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…
In this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, th…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

708 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now