Hi,
I have an sql statement that runs a search query that splits a field PRMF by semicoloms ";", the splitted field properties create seperate records and displayed as such on field SubString,
This query is run from a form and the user is allowed to select a choice in a combo box.
The problem is i have a command button which by a press of a button should append the users choice on form view But i am having problems with it, i want it to append that specific SubString and all other fields with that record chosen.
sql that splits field (works):
SELECT I1.ID, I1.postcode, Mid$(";" & I1.PRMF & ";", S1.num + 1 , S2.num - S1.num - 1) AS SubString, I1.STR, I1.CNT
FROM ma_dbo_uk AS I1, integers AS S1, integers AS S2
WHERE I1.postcode Like [Enter Postcode:] AND
Mid$(";" & I1.PRMF & ";" ,S1.num , 1 ) = ';'
and Mid$(";" & I1.PRMF & ";" ,S2.num , 1 ) = ';'
and S1.num+1 < S2.num
and instr(1,Mid$(";" & I1.PRMF & ";", S1.num + 1 , S2.num - S1.num - 1),";")=0;
What i have so far is for the append; event procedure(needs changing):
Dim strSQL As String
strSQL = "INSERT INTO ma_enq ( A4, A1, A2, A3 ) " & _
"SELECT ma_dbo_uk.postcode, ma_dbo_uk.SubString, ma_dbo_uk.STR, ma_dbo_uk.CTN " & _
"FROM ma_dbo_uk LEFT JOIN [ma_enq] ON ma_dbo_uk.postcode = [ma_enq].A4 " & _
"WHERE (((ma_dbo_uk.postcode)=[Forms]![Ma_search2]![PRMF_a1]));"
DoCmd.RunSQL strSQL
Any help very much appreciated,
M-.
I have an sql statement that runs a search query that splits a field PRMF by semicoloms ";", the splitted field properties create seperate records and displayed as such on field SubString,
This query is run from a form and the user is allowed to select a choice in a combo box.
The problem is i have a command button which by a press of a button should append the users choice on form view But i am having problems with it, i want it to append that specific SubString and all other fields with that record chosen.
sql that splits field (works):
SELECT I1.ID, I1.postcode, Mid$(";" & I1.PRMF & ";", S1.num + 1 , S2.num - S1.num - 1) AS SubString, I1.STR, I1.CNT
FROM ma_dbo_uk AS I1, integers AS S1, integers AS S2
WHERE I1.postcode Like [Enter Postcode:] AND
Mid$(";" & I1.PRMF & ";" ,S1.num , 1 ) = ';'
and Mid$(";" & I1.PRMF & ";" ,S2.num , 1 ) = ';'
and S1.num+1 < S2.num
and instr(1,Mid$(";" & I1.PRMF & ";", S1.num + 1 , S2.num - S1.num - 1),";")=0;
What i have so far is for the append; event procedure(needs changing):
Dim strSQL As String
strSQL = "INSERT INTO ma_enq ( A4, A1, A2, A3 ) " & _
"SELECT ma_dbo_uk.postcode, ma_dbo_uk.SubString, ma_dbo_uk.STR, ma_dbo_uk.CTN " & _
"FROM ma_dbo_uk LEFT JOIN [ma_enq] ON ma_dbo_uk.postcode = [ma_enq].A4 " & _
"WHERE (((ma_dbo_uk.postcode)=[Forms]![Ma_search2]![PRMF_a1]));"
DoCmd.RunSQL strSQL
Any help very much appreciated,
M-.