Modules & VBA :: Possible To Create A Form And Procedure Within That Particular Form
Oct 10, 2013
Is it possible to create a form and vb procedure within that particular form (ie. an OnClose event)?
Code:
Function CreateForm()
Dim Form as Form
set Form = CreateForm
frm.Caption = "MyNewForm"
frm.Recordsource = "Stuff"
'Do other stuff to form, save and close, open form again
[Forms]![MyNewForm].OnClose = 'insert sub and/or call to procedure (if possible, haven't been successful yet)
End Function
View Replies
ADVERTISEMENT
Jan 15, 2015
Following concerns about someone accidentally deleting the access database we have been using to crunch performance numbers, I have successfully moved the data on to an SQL server
While the database works as it is, several of the queries are running extremely slow. I therefore decided to see if a stored procedure could run the number crunching on the server instead of passing the data back and forth all the time.
I have taken the series of queries and converted them into a stored procedure that runs too fast for me to blink while giving the same results as before.
The problem I face is that I can trigger the stored procedure from the server management studio manually while supplying the variables needed thus providing the data I need to export to excel in a table for this purpose.
What I want to do is to have a form in access supply the chosen variables (like I could before) and run the stored procedure at the click of a button as part of a series of other queries.
I have looked at pass-through queries but apparently they do not take kindly to variables unless they are hardcoded. The other solution would be to trigger it from VBA but I have not been able to find a solution I could get to work.
How to run a stored procedure on an SQL server from access while also giving it the variables it needs?
Stored procedure name: spNearMissCalculation
Variables:
@SelectedDate (date format) (taken from a form field)
@SelectedVessel (nvarchar(max) format) (taken from a form field)
@SelectedVesselGroup (nvarchar(max) format) (taken from a form field)
View 4 Replies
View Related
Jul 31, 2014
I have just made a change to one of the forms by adding a button (by copying the only other button on the form) to cancel any changes and close the form. However, as soon as I added it I started getting the error message in the title. Please attachment LA Err1 for the full message. I also changed the caption on the other button on the form from "Close Form" to "Save && Close Form" this button is now giving the same error.
I have Compacted and repaired the DB on several occasions to no avail. I have deleted the procedures from the module and recreated them using the properties window - still get the error. I have deleted the buttons from the form and recreated the both via the object wizard and without it. Nothing I have tried has made any effect.
View 5 Replies
View Related
Apr 6, 2015
I need Create a new counter on my Customer form for each day, using an Autonumber and print it
View 6 Replies
View Related
Nov 28, 2014
I'm creating a DB that includes a form that "Customers" will fill out. The form (CustomerForm) has text boxs that must be completed. I'm trying to figure out how to make sure all text boxes are filled out. I have already created a Input Mask and selected Required in the "Customer" Table. There is also a Username text box I would like to have validated that is is not already being used and two password text boxes I would like to have validate against one another to ensure it was created correctly. Once all text boxes are properly filled out I would like the Button to Save customer data, close CustomerForm and Open ShoppingCart. This is the code I have created:
Code:
Option Compare Database
Private Sub Log_In_Click()
If IsNull(Me.CustomerFName) Then
MsgBox "Please Enter First Name", vbInformation, "First Name Required"
Me.CustomerLName.SetFocus
[code]...
View 7 Replies
View Related
May 7, 2014
What I need to do is press a button on a form. When the button is pressed, I want to create a new record in a table not already open and populate some fields with data from the form I am looking at. Then call up a new form with the record I just created on it.
Basically it is a work-order entry issue, the user scrolls through to find the correct piece of equipment when they do they click on "Create work-order" a work order is created and populated and the user can then fine tune the new work-order as required.
View 13 Replies
View Related
Aug 30, 2006
Hi,
Is it possible to call a procedure that exists in one form on another form please?
Regards,
B
View 13 Replies
View Related
Feb 25, 2014
I have been trying to create a login form that allows the user to change his/her temporary password logging it to the proper table along with timestamp and who done it info.But, after spending the morning trying to find the proper syntax I am flummoxed.
I can get everything to work accept the update of the fields. I can get the command to work (writes to the location) but it does the pop-up what is the parameter thing when it works. I have all the information just need to get it in so the command recognizes it.
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE lut_TeamList SET Pass = txt_Password.value WHERE TeamListID = Me.cbo_UserName.Value"
DoCmd.RunSQL "UPDATE lut_TeamList SET UpdatedBy = Me.cbo_UserName.Value WHERE TeamListID = Me.cbo_UserName.Value"
DoCmd.RunSQL "UPDATE lut_TeamList SET UpdatedWhen = Now() WHERE TeamListID = Me.cbo_UserName.Value
DoCmd.SetWarnings True
View 5 Replies
View Related
Jul 22, 2015
I am trying to create a form button that will automatically email each row of a query result to myself. At first the VBA code worked fine with a standard query. However when I use it with a query that contains a reference to a combobox form such as "<=[Forms]![Reminder]![Monthsleft].[Value] And >=0" I get the 3061 run-time error and "Too few parameters. Expected 1." I have included the VBA code below.
Private Sub Command9_Click()
Dim MyDb As DAO.Database
Dim rsEmail As DAO.Recordset
Dim sToName As String
Dim sSubject As String
Dim sMessageBody As String
[code]....
If you intend to renew the lease, terms and conditions will need to be submitted for ECC for approval (regardless of changes or not in lease rates). If the terms have yet to be confirmed, it is important to begin the negotiation process as soon as possible with a target to provide the ECC submission at least two months prior to the commencement date of the renewed lease. To ensure sufficient time for ECC approval before the contract expiry date, please prepare the ECC paper and obtain necessary endorsements. Submission details can be found here. The ECC submission template and PSD Questionnaire could be found from this link
DoCmd.SendObject acSendNoObject, , , _
sToName, , , sSubject, sMessageBody, False, False
.MoveNext
Loop
End With
Set MyDb = Nothing
Set rsEmail = Nothing
End Sub
View 2 Replies
View Related
Dec 19, 2005
Hi All
Im having some Major issues with this problem, i only hope someone can help me!!
Firstly, let me set the scene. I have 1 Form, And 1 Subform within that form. The form gets the Main Data, the SubForm gets all the Related Data (in this case, its all the Items Of Equipment this client has) and then lists them in the subform. So it could return 1, or it could return 20 records.
What we want from this is to be able to record what the client thought of the piece of equipment, so there is a Option Group on the SubForm, which allows the user to choose 1 of 6 options saying whether it was useful or not and things.
BUT here lies the problem, if the SubForm has returned 20 records, i change the OptionGroup on record one, and every other 19 records become the same. if i change Record 2, record 1 and 3 to 19 are the same.
I want to be able to have a seperate choice for each record returned, but tis not working!!
The subform is populated by calling a SQL SP, i cant set the form to link directly to a view or SP because access doesnt allow me, by saying the recordset is not updatable!
I hope all that makes sense!!
View 1 Replies
View Related
May 6, 2005
I'm trying to use code from a project that came with an Access book. However, I can't get it to execute, because I can't get it to compile.
Could someone PLEASE help me out?
Private Sub Search_Click()
gstrWherePhys = ""
If Not IsNothing(Me!LastName) Then
gstrWherePhys = "[LastName] Like " & Chr$(34) & Me!LastName
If Right$(Me!LastName, 1) = "*" Then
gstrWherePhys = gstrWherePhys & Chr$(34)
Else
gstrWherePhys = gstrWherePhys & "*" & Chr$(34)
End If
End If
If Not IsNothing(Me!FirstName) Then
If IsNothing(gstrWherePhys) Then
gstrWherePhys = "[FirstName] Like " & Chr$(34) & Me!FirstName
Else
gstrWherePhys = gstrWherePhys & " AND [FirstName] Like " & Chr$(34) & Me!FirstName
End If
If Right$(Me!FirstName, 1) = "*" Then
gstrWherePhys = gstrWherePhys & Chr$(34)
Else
gstrWherePhys = gstrWherePhys & "*" & Chr$(34)
End If
End If
If Not IsNothing(Me!City) Then
If IsNothing(gstrWherePhys) Then
gstrWherePhys = "[City] Like " & Chr$(34) & Me!City
Else
gstrWherePhys = gstrWherePhys & " AND [City] Like " & Chr$(34) & Me!City
End If
If Right$(Me!City, 1) = "*" Then
gstrWherePhys = gstrWherePhys & Chr$(34)
Else
gstrWherePhys = gstrWherePhys & "*" & Chr$(34)
End If
End If
If Not IsNothing(Me!Facility) Then
If IsNothing(gstrWhereCust) Then
gstrWherePhys = "[Facility] Like " & Chr$(34) & Me!State
Else
gstrWherePhys = gstrWherePhys & " AND [Facility] Like " & Chr$(34) & Me!State
End If
If Right$(Me!Facility, 1) = "*" Then
gstrWherePhys = gstrWherePhys & Chr$(34)
Else
gstrWherePhys = gstrWherePhys & "*" & Chr$(34)
End If
End If
If Not IsNothing(Me!State) Then
If IsNothing(gstrWherePhys) Then
gstrWherePhys = "[State] Like " & Chr$(34) & Me!State
Else
gstrWherePhys = gstrWherePhys & " AND [State] Like " & Chr$(34) & Me!State
End If
If Right$(Me!State, 1) = "*" Then
gstrWherePhys = gstrWherePhys & Chr$(34)
Else
gstrWherePhys = gstrWherePhys & "*" & Chr$(34)
End If
End If
If IsNothing(gstrWhereCust) Then
MsgBox "No criteria specified."
Exit Sub
End If
Me.Visible = False
DoCmd.Hourglass True
If IsLoaded("PhysicianDB")
Forms!PhysicianDB.SetFocus
DoCmd.ApplyFilter , gstrWherePhys
If Forms!PhysicianDB.RecordsetClone.RecordCount = 0 Then
DoCmd.Hourglass False
MsgBox "No Customers meet your criteria"
Me.Visible = True
Exit Sub
End If
Else
DoCmd.Hourglass True
DoCmd.OpenForm FormName:="PhysicianDB", WhereCondition:=gstrWherePhys, _
WindowMode:=acHidden
If Forms!PhysicianDB.RecordsetClone.RecordCount = 0 Then
DoCmd.Hourglass False
MsgBox "No Customers meet your criteria"
DoCmd.Close acForm, "PhysicianDB"
Me.Visible = True
Exit Sub
End If
DoCmd.Hourglass False
End If
DoCmd.Close acForm, Me.Name
End Sub
View 12 Replies
View Related
Jul 25, 2005
I have a form with a subform and on the form and also on the subform, I have three comboboxes or txtboxes and in the OnExit event I have a little procedure which makes sure that each have been filled in or it asks if you want to cancell and if yes, it setfocus on the cmdCancell and then I can canells the records, and the procedure looks like this:
Me!QCTypes_Label.ForeColor = 0 'Change Label Color to Black
Dim Response As VbMsgBoxResult
If Len(Nz(Me.QCTypes, "")) = 0 Then 'If QC Type is Blank
Response = MsgBox("No QC Type Was Entered," & vbCrLf & "Do You Want to Cancel QCA.", vbYesNo + vbDefaultButton2, "No?")
If Response = vbYes Then
Me.cmdCancell.SetFocus 'SetFocus on cmdCancell
Exit Sub 'Exit Immediately
Else
Me!QCTypes_Label.ForeColor = 33023 'Change Label Color to Orange
Cancel = True 'Re-Enter QC Type, same field
End If
End If
Now the main form works great, but the subform acts strange, when I select YES, instead of cancelling and closing like the main form, this one does each procedure for each of the other two procedures. Why does it work right on the main and not on the subform? Thank you in advance for any assistnace rendered. :eek:
View 14 Replies
View Related
May 10, 2006
I have a sub form which has the Save Button. In case the user keys in the details in the sub form, but then directly clicks the Payment command button of the main form, I want to call the Save button in the sub form if the form is dirty, and do the save, otherwise, proceed with the payment command button.
Every time I try to call the save procedure from the main form's Payment button, I get this error that "object does not support this property or method"
I am calling the save button's code in the subform as
forms.MainForm.SubForm.Save Producedure
Exact Code is : Forms.newpatients.InvoiceHeader.Command7_Click
How do I call this command7_click, which resides in the sub form, in the main form, and invoke it only if the data in the sub form has changed.
Please help.
Thanks,
Vinai
View 1 Replies
View Related
Jun 29, 2006
Hi All,
I created an Event Procedure on the After Update event for a control in a continuous form. Basically, if certain conditions are met, I want it to disable another control. It works perfectly, except it is disabling the controls for all the records instead of just that record.
Does anyone know how to get it to just update that record? I've been researching and cannot figure it out!
Thanks in advance for any and all help :)
Krysti
View 3 Replies
View Related
May 3, 2013
if you are in a subform, can you pass the subform as a form to a procedure.
eg
msgbox(me.name) 'shows the form name correctly, but then
call somefunc(me) doesn't seem to work.
I get a type mismatch in
function somefunc(frm as form)
E.g.
function somefunc(frm as object)
msgbox typename(frm) 'returns "Controls"
end function
View 14 Replies
View Related
Aug 10, 2013
I am trying to create a user form with sub form using the form wizard. I have only 2 tables, Employee (main table) and Vacation (subform table). I pick the fields from Employee Table then fields fro Vacation, but the wizard treat the Vacation as main form and Employee as sub form.
View 4 Replies
View Related
May 28, 2014
I have an issue with a BeforeUpdate procedure that I am trying to get working. I'm using simple elements here as this is a test DB, not the real one yet.
I'm trying to get the value from a Text Box TextData into a string cString. I've used a Dim statement to declare cString - I think the problem I am having is trying to get the actual data from the form. The error I am getting is:
Run Time error '2465'
Microsoft Access cannot find the field "|1" referred to in your expression.
The code I am using is:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim cString As String
cString = [Forms!FormData!TextData]
End Sub
(I've removed some code that is either comments or working since I have to retype things!)
The line cString = [Forms!FormData!TextData] is where the debugger highlights as the error.
View 3 Replies
View Related
Jan 28, 2015
I have a stored procedure created in SQL SERVER 2008r2
I have a form in access adp project with combo boxes, when I click the submit button I want the values chosen to be the parameters and the stored procedure called to generate a report
Is this possible .
View 1 Replies
View Related
Mar 20, 2014
What I would like to do is create a list box that will only display the information from another form that is related to the current form.So I have a form call Equipment Catalog and that form is related to Equipment features 1 to M relationship and the Equipment Features is related to a Features form M to 1.
So what I want to do is display all the related equipment features in a listbox that is related to the current PK of that form.So if there is only one feature on one form the list box will only display that one item however is there is 6 features on another it will display all 6.I have been trying SQL and Queries but I still can't get it to work.
View 1 Replies
View Related
Feb 3, 2014
The user will be creating a new project that contains a bunch of releases. The releases have standard names which are stored in a table tbl_ReleaseNames It should be noted that the list of names is not static.
The user selects which of the releases pertains to their project and then based on their selections, new records would be created in tbl_RFP_Release and then a subsequent form would open where it would display each of these newly created releases where they could enter additional information. I thought of creating an unbound checkbox associated with each of the standard names, and then checking to see if the checkbox was checked and then creating the new records followed by opening up the new form.
View 6 Replies
View Related
Sep 25, 2013
I've built a PostgreSQL database for some ecology data and am trying to use MS Access 2010 to make a front-end for it.
I've created a form to display site data; it includes such fields as site ID, site name, county, state, landscape, etc. (There are more, but if I can get the idea down with a couple of these, I should be good to handle the rest). One can click through the form and see 14 pages of site details, which is fine because there are 14 sites.
What I want to do now is make a search form in which one can enter values for site ID, site name, county, etc, and somehow get the pages of the display form that match those values. It's not terribly important to me how that output looks - whether it's a list of matches that allows for clicking on a match and showing just one page of the display form, or whether it's a filtered subset of the display form pages that one still clicks through to view them all.
if there is code to write, where do I type it? Do I start my search form by making a form with the search fields as values, or is there some other way? How do I make those search fields into search boxes rather than display boxes?
View 14 Replies
View Related
May 13, 2014
I am trying to create a form to enter data in a table. I would like to make it pull in info from a switchboard. If the record already exists I would like it to find it and allow me to edit the info. If the record doesn't exist I would like to be able to add a new record with the data input. What is the best way to accomplish this?
View 1 Replies
View Related
Feb 2, 2014
I have 2 tables and I can create 2 forms from them but when I try to use both tables to create one form the form is blank.
View 2 Replies
View Related
Apr 28, 2014
I have a form with about 100 labels and would like something to happen when I mouseover. So I have a following code:
Code:
Private Sub Label1_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
Label1.BackColor = vbBlue
End Sub
[Code]....
View 7 Replies
View Related
May 11, 2015
I'm having an issue getting a return value from a stored procedure that I'm calling from VBA. This is what I have at the moment:
Code:
Dim strDate As String
Dim strWOStatus As String
Dim CurrentConnection As ADODB.Connection
Dim adoCMD As ADODB.Command
Dim adoRS As ADODB.Recordset
Dim ParamReturn As ADODB.Parameter
[Code] .....
The problem I am having is this error:
Error: 424
Description: Object Required
The line of code it errors on is:
Code:
Set .Parameters("@PartsUSedMTD").Value = ParamReturn
And the value of ParamReturn is always Null after it hits the line before it.
So it seems like it's not really creating the parameter variable SQL Server needs to run
View 4 Replies
View Related
Mar 21, 2014
This is a real conundrum. The following procedure works well the first time through but on subsequent times the database just goes into "Not Responding" mode and has to be restarted. I have tried putting delays in but with little success.
The idea of the procedure is to rename a query, "Daily Logs" to include a specific date "TransDate" so that when the query is sent via Outlook the Excel file name appears for example as Daily Logs 21-03-14.
DoCmd.CopyObject "", "Daily Logs " & Format(Me![TransDate], "dd-mm-yy"), acQuery, "Daily Logs"
DoCmd.SendObject acQuery, "Daily Logs " & Format(Me![TransDate], "dd-mm-yy"), "Excel97-Excel2003Workbook(*.xls)", "someone@dorsetglidingclub.co.uk", "", "", "Daily Logs " & Format([TransDate], "dd-mm-yy"), "", True, ""
DoCmd.DeleteObject acQuery, "Daily Logs " & Format(Me![TransDate], "dd-mm-yy")
This procedure might seem clumsy but was the only way I could think of the achieve the objective.
View 10 Replies
View Related
