UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Insert Record Using Parameter Query And Macro On A Form., Access 2010    
 
   
akhlaq58
post Jul 7 2019, 07:23 AM
Post#1



Posts: 4
Joined: 30-June 19



Hi, I have to insert record in a Subform by pressing button on Mainform using macro which can run append query with parameters or call a procedure. Following is append query which is successful by promoting for user input. But I want it to take parameter value from Mainform specified control.
INSERT INTO Fee_Recv ( Amount, Month1, Type, Stud_ID )
SELECT Students.Fee, "JUN" AS Month1, "TF" AS Type , [:st_id] AS Stud_ID
FROM Students WHERE (((Students.ID)=[:st_id]));
Subform is based on table "Fee_Recv". Please write down steps for creating macro or just a macro code. Also please write down a procedure if it is better to use instead of append query.
Go to the top of the page
 
GroverParkGeorge
post Jul 7 2019, 08:49 AM
Post#2


UA Admin
Posts: 35,502
Joined: 20-June 02
From: Newcastle, WA


Welcome to UtterAccess.

What have you tried so far?

Also, you indicate that there is a field named :st_id in your table. We strongly advise against using characters like the colon in field names. It's a source of potential confusion. Change that to st_id (or even better, use the alias you chose here as the real, permanent name for the field) and save yourself some grief down the road.

You can do this in several different ways, and you can do it either with a macro or with VBA. Why would you opt for a macro? VBA offers more flexibility and better error handling. Macros are handy for very simple tasks like opening or closing forms, although they can handle this task, I suspect.

Create three TempVars in your macro. One for the Month, one for the type and one for the Student ID.

Change the SQL from hard-coded values for the three criteria to your TempVars. Execute the query.

I wonder, though, why you are approaching this task in this way.

Your subform is bound to the table into which your query would insert a new record. Somehow you have to be picking a month and "Type" somewhere. So why not just use the subform directly--as it is supposed to be used--and add the month and Type there?

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
akhlaq58
post Jul 7 2019, 07:26 PM
Post#3



Posts: 4
Joined: 30-June 19



Thank you for your reply. First of all there is no column with name [:st_id]. This is for a parameter in query. With this, query will ask user to enter the value. After taking the value, it will show the result. In this case it will ask for student ID whose fee need to be chosen. Secondly why I chose to have a macro? My previous experience is Libreoffice with Mysql in back end. And I mentioned that a macro or a procedure. Third why I want to do that? Why not entering values directly in subform. This is just time saving. In real environment, there is rush of students who submit their fee. So with one click 4 values will be inserted. If user have to enter other fee type or month, he will choose from the drop down lists. Because I am not familiar of VBA language so I requested it for me as shown in query. Although I tried to write a procedure but failed.
This post has been edited by akhlaq58: Jul 7 2019, 07:28 PM
Go to the top of the page
 
akhlaq58
post Jul 8 2019, 05:31 AM
Post#4



Posts: 4
Joined: 30-June 19



I have found the solution by creating a VBA procedure using help from google search. Here is the code.
Private Sub InsertFee_Click()
DoCmd.RunSQL "INSERT INTO Fee_Rec(Amount, Month1,Type, Stud_ID) SELECT Students.Fee 'Jun' AS Month1, 'TF' AS type, Students.ID FROM Students WHERE [ID] = Screen.ActiveForm![ID];"
DoCmd.SetWarnings False
DoCmd.RefreshRecord
End Sub
Before I was using a macro code in Libreoffice Base that was almost 15 lines long just to run a Query. I was sure that MS Access must have a simple solution for that.
Go to the top of the page
 
dale.fye
post Jul 8 2019, 06:35 AM
Post#5



Posts: 128
Joined: 28-March 18
From: Virginia


I would advise against using this syntax because using the SetWarnings False syntax can cause problems. In the example you posted, you set the warnings to False after you actually run the insert statement, so it will have no positive effect. Additionally, once you set SetWarnings False you should always turn it back on (SetWarnings True). Because your code doesn't do that, if you revert to design view and do some work, you will not receive a warning when you execute another action query. If you really want to use this syntax, then it should look like:

CODE
Private Sub InsertFee_Click()
docmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO Fee_Rec(Amount, Month1,Type, Stud_ID) SELECT Students.Fee 'Jun' AS Month1, 'TF' AS type, Students.ID FROM Students WHERE [ID] = Screen.ActiveForm![ID];"
DoCmd.SetWarnings True
DoCmd.RefreshRecord
End Sub


The other reason I advise against this is that if the query fails, you will not receive any warning. Instead, I would use something like the following:

CODE
Private Sub InsertFee_Click()

    dim strSQL as string

    On Error Goto ProcError

    strSQL = "INSERT INTO Fee_Rec(Amount, Month1,Type, Stud_ID) " _
                & "SELECT Students.Fee 'Jun' AS Month1, 'TF' AS type, Students.ID " _
                & "FROM Students WHERE [ID] = " & me![ID]"
     currentdb.execute strsql, dbfailonerror
     me.subformControlName.Form.Requery

ProcExit:
    Exit Sub
ProcError:
    msgbox err.number & vbcrlf & err.description, , "Insert Fee error"
    debug.print "Insert Fee Error"
    Resume ProcExit
End Sub
This takes advantage of the execute method, which gives you the ability to flag errors which occur during the insert process.

Note: after the execute method, I requery the subform control with the following code:
CODE
me.subformControlName.Form.Requery

You should replace "subformControlName" with the actual name of the subformcontrol.

--------------------
Dale Fye
Microsoft Access MVP 2013-2016
Developing Solutions, LLC
Go to the top of the page
 
akhlaq58
post Jul 10 2019, 12:22 AM
Post#6



Posts: 4
Joined: 30-June 19



Thank you very much Sir!
You have written a professional code when my code is just a layman code extracted from google search. I tried it and faced too many errors from debugger. At last the final one is " Too few Parameters, Expected 1". One point I could not understand is "me.subformControlName.Form.Requery". Why subform? The form has Mainform and [ID] is on this main form which need to be remain on screen after completing the code. If it must be the subform then which control? My subform has 5 controls. Or just enter the subform name?
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    17th August 2019 - 07:54 AM