MS ACCESS - changing data dynamically in the form

Question

My form is divided by Form Header, Detail section and Form Footer (design mode). All table fields are listed in Detail section.

I have to make it works this way - when I am clicking on person's name (shown as hyperlink) Detail section has to show me records from table, where name field value is equal one that I clicked. The number of the shown fields should stay the same.

I wrote a procedure on VB:

Private Sub GE_PERSON_Click()

Dim strSQL As String

strSQL = "SELECT GENERIC.GE_ID, GENERIC.GE_OPEN AS [OPEN], Workforce.WF_NAME AS PERSON, [GENERIC].[GE_DATEIN] AS RECEIVED, GENERIC.GE_DATEREQUESTED AS [STARTING DATE], GENERIC.GE_CONSECUTIVE AS CONSECUTIVE, GENERIC.GE_AMOUNT AS [DAYS TAKEN], ABSENCE.AB_TYPE AS [ABSENCE TYPE], [GENERIC].[GE_STATUS] AS STATUS"
strSQL = strSQL + "FROM (GENERIC INNER JOIN Workforce ON GENERIC.[GE_PERSON] = Workforce.[WF_ID]) INNER JOIN ABSENCE ON GENERIC.[GE_TYPE] = ABSENCE.[AB_ID]"
strSQL = strSQL + "WHERE WORKFORCE.[WF_NAME] = " + Form_GENERIC.GE_PERSON
strSQL = strSQL + "ORDER BY GENERIC.GE_DATEREQUESTED;"

Form_GENERIC.RecordSource = strSQL 

End Sub

When I assign the strSQL to the Form_GENERIC.RecordSource I receive an error. I can't send data from formed string to the main form GENERIC Record Source - it causing error

Runtime Error 3141:The SELECT statement include reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect

Definitely need your help guys!

How are related records displayed? Is there a subform? Your form description is not sufficient to generate a definitive answer. — MoondogsMaDawg, Dec 13 '16 at 14:07
Let me see if I got this... When you click the `NAME` field, it will filter the current form(You didn't mention if there is a subform, if its a divided subform or what will show the filtered records). Assuming it is in spreadsheet mode. — Lybren, Dec 13 '16 at 14:21
[How to debug dynamic SQL in VBA](http://stackoverflow.com/questions/418960/managing-and-debugging-sql-queries-in-ms-access/1099570#1099570) — Andre, Dec 15 '16 at 13:17

Tony · Answer 1 · 2016-12-15T10:04:44.583

When you concatenate strings make sure you include a space at the beginning or end of each line. Otherwise the strings are pushed together causing the error you are seeing.

E.g. if you do this

strSQL = "SELECT field1, field2"
strSQL = strSQL + "FROM table"

You end up with this

"SELECT field1, field2FROM table"

Add a space to prevent the error

strSQL = "SELECT field1, field2"
strSQL = strSQL + " FROM table"
                   ^
                   put a space at the start of each concatenated line

MS ACCESS - changing data dynamically in the form

1 Answers1