Determine which record was clicked and get values of certain fields

[DonVojtos]

Hello, I have a form (its name: frmCitaceNew) which contains a subform (its name: sfrmRecords). Subform's record source is a query (its name: qryQuote). What I'm trying to do, when I double-click a certain record (row) in the datasheet and obtain values of 2 columns (names: IDOrig2 and DocumentType). Those values are assigned to other variables IDOrig and DocType.
Help would be appreciated.

 

[MajP]

I assume this is an Access question. You can get better results by posting in one of the several Access forums? If this is Access then simply call the procedure from one or more of the form controls.

private Sub SaveValues
   dim IDOrig as String
   dim DocType as string
   IDorig = me.IDOrig2
   DocType = me.DocumentType
   'addtional code to do something
end Sub

However, once you save the values to variables you did not say what you are doing with the variables. The above variables are local to the sub and go out of scope once the sub ends. If you want them to persist for the life of the form than declare them at the top of the form instead of inside the procedure. If you want them to persist for the life of the session then declare them instead in a standard module. Ex

public IDOrig as string
Public DocType as string

private Sub SaveValues
   IDorig = me.IDOrig2
   DocType = me.DocumentType
   'addtional code to do something
end Sub

 

[DonVojtos]

Thank you for your help, MajP. Especially, the advice to declare the variables at the top. You guessed it right; it was an Access question.

Goal: On dbl-click certain record in form's subform initiate Sub QuoteSequence.
Forms: frmQuotes - main form
sfrmQuotes - subform, in datasheet view, source: tblAllRecords (eventually I didn't use query as I had stated, I simply used the table)
txtBoxQuote - textbox where the quote will show up
In steps: Create a form "sfrmQuotes" - data source: tblAllRecords (drag over only desired columns)
Create a form "frmQuotes" - on it create subform, as source object select "sfrmQuotes", name it "sfrmQuotes"
on it create a textbox and name it "txtBoxQuote"
'applying on dbl-click event on sfrmQuote
'go into Design view
click on each textbox (not the note, the textbox) representing column and click on dbl-click event
in VBA in the top declare: Dim IDOrig As Integer (the value was a number)
Dim DocType As String
in each on dbl-click event put: IDOrig = Me.IDOrig2.Value (this will get you a value from column 'IDOrig2' and save it as IDOrig no matter which field you click)
DocType = Me.DocumentType.Value (this will get you a value from column 'DocumentType' and save it as DocType no matter which field you click)
underneath create a private Sub QuoteSequence
'for example the quote consists of only 2 strings
declare strings: Dim Str1, Str2, Quote As String
Str1 = DLookup("Name","tblAllRecords","[ID] = " & IDOrig) 'this will look up for you what value is in column 'Name' in table 'tblAllRecords' where value of column 'ID' correspond with IDOrig of selected record), Note: ID was a datatype of Number so probably Integer or Long hence the IDOrig was set up as Integer as well (I don't know if it's a must though...)
Str2 = DLookup("Subject","tblAllRecords","[ID] = " & IDOrig) 'will look up a value in column 'Subject' for selected record
Quote = Str1 & " " & Str2
Forms!frmQuotes!txtBoxQuote.Value = Quote 'the textbox was created on the main form frmQuotes therefore we have to specify exact location
End Sub
Hopefully it's comprehensible even for beginners as myself

 

[MajP]

If I understand this correctly (which I am not sure I do) there may be a much simpler process. It sounds to me that you have a self referencing table. You have a field IDOrig that relates back to another record in the same table. If that is the case all you have to do is populate IDOrig and you can automatically get the quotes for every record.

To do this you go to the query builder and select the table allRecords twice and link it by ID to IDOrig. Alias the tables with unique names or Access will give them the names tblAllRecords and tblAllRecords1. I would alias the one on the left AllRecords and the one on the right Originator. Now do a left join from AllRecords to Originals. From the originator table choose only the fields Name and subject. Pick all the other records from AllRecords. Now simply make a calculated field.
CalcQuote:[Name] & " " & [Subject]

Since this is a calculated field you will have to do this query every time for display. Or if you want it to persist than you can simply put in the forms on current event. Me.Quote = me.CalcQuote.