I have a form displaying the contents of a table in a datasheet view. I want to click on a row and open up a new form that will use a field in the selected row as a parameter.
I know how to call the VBA procedure by setting the double click on field (Cant do it for click on any fields in the row?) and in the VBA was using:
strText = Me.[Product Reference].Value
to get the required value. However this only seems to work in single form view. If it is in datasheet view it gives an error and will not let you save the form.
I am guessing I am missing the row identifier in my VBA code.
Not sure which forum this was under, but I figure Access might be it
I have a stored procedure already written which works fine, if I supply the criteria to it before or at manual execution. I want it to use a field on a form in an Access Data Project as it's criteria (as a form is built off the results of the procedure). I can't find any documentation on how to pass criteria to a stored procedure for use in SELECT WHERE statements.
Can somebody point me in the right direction? Here is my stored procedure:
When i execute this manually I get the dialog prompt to enter the value for @parHomePhone, which is what I want to automatically pull from txtPhone on the frmSearch form.
The procedure call "Call RearrangeCoordinates(RearrangeCoords(), ReturnCoords())" return a message "Type mismatched: Array or user-defined type expected"Are there any views as to why the message is being displayed?
Private Sub CommandButton1_Click() Dim ReturnCoords As Variant Dim ........................................... ArrayLength = UBound(ReturnCoords)
On my form I have a combo box where the user can select the sex of a person. I had set the row source of the combo to a table, tblSex and this works fine. Now I want to programatically add the table entries ( simply male or female) to the row source, having set the combobox to value list. I cant see how to pass the combobox and tablename parameters and I am getting an error on form load "The expression you entered as the event property setting produced the following error: UserDefined type not defined" The calling procedure from frmPeople is
I think the way the parameters combobox and tablename are passed is incorrect. I want to write a robust procedure which I can use for each of the comboboxes on my form.
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.
I currently have a table showing activity for multiple staff and their availability throughout the day in 30 minutes segments.
I am currently trying to pull the information on who is working by 30 minute timeslice, but as the information is held in a different field for ech period, it is proving difficult.
My thought was to make a query rounding the current time to the nearest hour/half hour and use this to choose the field, but I don't know how to make a query which will allow me to pass a variable (Field name) into the Select query?
I wonder if anyone can help me? I am at the stage now of building a query in design view. Rather than using a dynamic parameter field to capture a range of dates (between...[InputDate] And [InputDate]), I have created a text box in a form and want to pass the contents to my query. I have got this to work providing the variable that is passed is 'text'. I need to pass two dates though. When I put paths to the forms textbox in the 'Between' statement above, it just doesn't return any records. I think Access sees these text boxes as 'text' rather than 'dates'. I don't know how to change it so Access sees these as dates. Any ideas?
I'm trying to create a database where a single "Classification" field is populated by selections made in a multiselect listbox and I can't figure out how to do this. Any help people can provide?
Table 1: Name_ID <pk> Name Classification
Table 2: Classification_ID <pk> Classification
What I want to happen is click on a button next to the Classification field (text) and a popup form with a multi-select list loads (this part is easy, of course). The user can select as many classifications as they want, click the ok button and each item selected then goes back to the first form and populate in the Classification field (seperated by commas or semi-colons). This possible?
OK - I have seen the other posts where individuals are trying to select multiple items from a list box and have a field populate with the selections. I have not seen a clear explanation defining if this is possible.
Essentially, I want to be able to query on the field and search for multiple selections within that field. Any recommendations as to how this can be achieved?
My next question, is if the selections in a multiple instance field are separated by a comma or some other character what is the best method to query for multile responses. For example, if the following data is in the field 1,2,3 and I want to query on 1 or 2?
I have a control on a form which opens up Word and completes fields in the Word document with data on the form. It basically fills in an invoice form.
Everything works ok apart from when the invoice date and order date fields are passed. In the tables and in the forms, the dates are in Long Format ie '17 May 2005'. However, when they are apssed to the Word document, they are in a shortened format ie '17/05/05'. I am required to show the Long Format but am at a loss to find out how to achieve this.
I use bookmarks in the Word document and the code on my form relating to these fields are:
The Word document cannot be changed as far as I can see. I believe the answer could be to change the code above to change the format, but cannot get anything to work?
I have "LossForm" to record loss of inventory items due to damage, theft, etc. It has "Loss Subform" for input of multiple items. The row has a calculated field "TotalLoss" (from qty * itemcost). The footer of subform has unbound text field =Sum(nz([TotalLoss])). This all works fine. The problem I have is that I need to pass the total to another form. I want to have a pop-up form to use some of the field values from the Loss form. I have been able to pass all of the field values except for the TotalLoss.
pop up form: Set frmPrevious = Screen.ActiveForm Me.TransactionID = frmPrevious.LossID Me.EntryType = "Loss" Me.Date = frmPrevious.LossDate { Me.Amount = frmPrevious.TotalLoss doesn't work } DoCmd.Save End Sub
I also tried to setup a global, class, and module variable but keep getting error message of undefined variable.
I have a button on a subform that becomes visible if there is no records in the source of the subform. When clicked I want to run a query that will insert a record on to the source of the subform. There is one field in the query that I need to get from the parent form.The first part works OK - the button is visible when the source file to the subform for this main form record, is empty.
If I run the query against the source file it inserts the new record after it has asked for the value of the variable field.My problem is that when I try to run the query when the button is clicked It can't find form![ClientFileFrm]![ClientId]..This is my code on the subform
Private Sub Form_Load() If Me.RecordSource <> "" Then If Me.Recordset.RecordCount = 0 Then Me.AddSettingsButton.Visible = True
[code]...
The ClientFileFrm is the main form.I can't seem to reference the clientId variable back to the main form.
My problem is as follows, i have created a report that calculates the total volume of FSC Materials. The user picks two dates from Calender controls that the report will range from. However the needs have now changed and i am required to make the report filter further based on user input, the problem i'm facing is that i cannot figure out a way to pass values from different variables to the report separate from another here is the code i would usually use to pass data to a query/report:
Code: Private Sub MonthlyFSC_Click() Msg = MsgBox("Select the Start and Finish Dates you wish to Query.", , "Start / Finish") Start = adhDoCalendar() Finish = adhDoCalendar()
[Code] ....
However i am now trying to do this, but it gives me an error as it is trying to pass the values to one field:
It is performing incorrectly within the case select and passing the wrong criteria, as it will only display results that meet the default values' criteria. However the date criteria is not be passed either.
I have several different sub-forms that have a button that opens a new form which creates a new record. Each of the different sub-forms have a field value that needs to be passed to the new record when the other form is opened. I've tried a few solutions, but to no avail. Right now I'm using the macro functionality as follows for one of the subforms:
The problem I think is that I'm creating a new record so the value doesn't get passed. The new record is only created after the user begins to enter data in the new form that was opened.
Currently, we have a table which contains a field for each type of training that can be taken (FUT1, FUT2, ...). Once an Instructor has taken one seminar, the table is populated with the date taken, via their employee number (the key) through an update form.
Now, the company wants the paper training reports to turn into a digital part of this database. As envisioned, the form and associated table will have combo boxes with pre-determined training types (FUT1, FUT2, ...). The date would be typed in.
The problem I see is getting the dates and training type from the future table to populate the existing table in the correct field for the correct Instructor.
In addition, there exists a report which prints out the training dates for each Instructor at each school. This is will still be in use.
Hi guys. i guess i am half way through. just like final step left still trying to figure it out how to create a VB procedure. anyways this is my theory and this is how much i have achieved. my God i have searched i guess every forum trying to get an answer it should'nt be that hard. I have a database. what i am trying to do is that i have a button for view reports which prompts the user to another form with 3 buttons 1) Issue(based on my query) 2) Issue Resolved(based on my query) 3) Issue not resolved(based on my query) i am using this onclick command Private Sub Issue_Resolved_Click() DoCmd.OutputTo acReport, "Issue resolved", acFormatXLS End Sub same thing for my other options. works fine but when i open the report in Excel the whole formatting is bad so with my research i found this code very valuable. a standard module code which is as folllow.
' // Generically formats a worksheet Private Sub GenericXLFormat(ByRef xlApp As Object, ByVal strDescription As String) On Error Resume Next With xlApp .Rows("1:1").Font.Bold = True .Cells.EntireColumn.AutoFit With xlApp.ActiveSheet.PageSetup .PrintTitleRows = "$1:$1" .PrintTitleColumns = "" .PrintArea = "" .LeftHeader = "" .CenterHeader = strDescription .RightHeader = "" .LeftFooter = "&""Arial""&8WF COF/SIP Database " & APP_VERSION .CenterFooter = "" .RightFooter = "&""Arial,Bold""&8CONFIDENTIAL" .LeftMargin = Excel.Application.InchesToPoints(0.5) .RightMargin = Excel.Application.InchesToPoints(0.5) .TopMargin = Excel.Application.InchesToPoints(0.8) .BottomMargin = Excel.Application.InchesToPoints(0.75) .HeaderMargin = Excel.Application.InchesToPoints(0.5) .FooterMargin = Excel.Application.InchesToPoints(0.5) .PrintHeadings = False .PrintGridlines = True .PrintComments = xlPrintNoComments .PrintQuality = 600 .CenterHorizontally = False .CenterVertically = False .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = False .FitToPagesWide = 1 .FitToPagesTall = False .PrintErrors = xlPrintErrorsDisplayed End With End With End Sub i tried to debug it and compile it no errors. now this is what i want and i will be done. what i want is when a user clicks on my report button which is (Issue) i want this Xlformat module to be run with my onclick button. i can't find or cannot think of any procedure since my skills in VB is not that very good. all i wanted to do is create some kind of procedure with this Xlformat module so then a user will be able to save the report as an excel file and then when he opens it the report in excel should be in perfect format. columns, width should be alligned automatically. i have been searching days and night with different answers to create a template or do this and that but nothing works plz help guys. i really appreciate it. Thanks
Can someone help? I am working on an event database (based on Microsoft 2003 event database template). At present when I register attendees, I can preview an invoice which is generated based on the information I have entered.
I have set up another option to preview a Letter of Confirmation based on this same principal. As I am "Visually Basic" challenged, I simply 'copied & pasted' the event procedure and made the changes as needed to ensure the correct report was opened (ie not the Invoice report but Confirmation Letter report).
However, when I click the button to open preview the letter, the "Print Invoice" box also opens up (as it does when previewing the invoice). I don't want this box to open as I don't need to enter any details.
Can someone please have a look at my VBA event below to see what I would need to delete to stop the "Print Invoice" box from popping up.
Private Sub LOC_Click() On Error GoTo Err_ConfirmationLetter_Click If Me![Attendees Subform].Form.RecordsetClone.RecordCount = 0 Then MsgBox "Enter attendee and registration information before previewing the Confirmation Letter." Else DoCmd.OpenReport "ConfirmationLetter", acPreview, , "[RegistrationID]=" & Forms![Attendees]![Attendees Subform].Form![RegistrationID] End If
Exit_ConfirmationLetter_Click: Exit Sub
Err_ConfirmationLetter_Click: If Err <> 2501 Then MsgBox Err.Description End If Resume Exit_ConfirmationLetter_Click End Sub
Many thanks for any help. Kath Price Auckland, New Zealand
PS - Below is the original 'Preview Invoice' event that I copied: Private Sub PreviewInvoice_Click() On Error GoTo Err_PreviewInvoice_Click If Me![Attendees Subform].Form.RecordsetClone.RecordCount = 0 Then MsgBox "Enter attendee and registration information before previewing the invoice." Else DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70 DoCmd.OpenReport "Invoice", acPreview, , "[RegistrationID]=" & Forms![Attendees]![Attendees Subform].Form![RegistrationID] End If
Exit_PreviewInvoice_Click: Exit Sub
Err_PreviewInvoice_Click: If Err <> 2501 Then MsgBox Err.Description End If Resume Exit_PreviewInvoice_Click End Sub
In my database I track a program called Bridge. Once you open bridge you can have anywhere from 1 to 7 "Sessions". Each bridge is assigned a number IE LT175A. If there are mulitple sessions they are numbered LT175A, LT175B, LT175C etc. I have a check box in my database that shows if bridge is installed on that PC. If it is then my section for session numbers are visiable, if bridge is not checked then the session numbers section isn't visiable. I currently have my Event Procedure in the After Update section. My code works because if I unclick on bridge my sessions disappears. However when I go to the next record if bridge is checked then my sessions are visiable but on the next record where it isn't checked it still displays the session section. So it isn't adjusting itself from record to record. Does my code need to go someplace else or am I doing something else wrong? Here is the code I have:(and it's under After Update)
This is not working however.... The table name is Response Tasks, the Column name is Response_ID and I am trying to pull the info from the one named "08". What am I doing wrong in the procedure?
Hi, I need to write a stored procedure that sits between sql server and MS access. This procedure will make sure that the queries run in access against the data in sql server will not access data for more than two years back. This is so that the queries run against the sql server do not hang up the server for too long.
I have a procedure which can put in a Restocking Charge, it works good but you have to type it in each time here it is: Dim strInput As String Dim strMsg As String strMsg = "Enter Restocking Charge?" & vbCrLf & vbLf & _ "Enter percentage." Select Case MsgBox("Is There a Restocking Charge?", vbYesNo Or vbExclamation Or vbDefaultButton1, Application.Name) Case vbYes Beep strInput = InputBox(Prompt:=strMsg, Title:="Value") Me.RestockingChg.Value = strInput Case vbNo Me.RestockingChg.Value = "" End Select I tried using the txtbox DefaultValue property and setting.DefaultValue and niether work very well. Is there a way to set the pop-up to display 15 or 15% when it opens and then be able to type over it? I can do that with a form, but the form open all ever the place and I couldn't get it to hit enter with out tabing or clicking the cmdButton. Thank you in advance to anyone helping out on this little bug. :eek:
I have implemented a logon script found in this forum - many thanks. In essance, the script opens a form with info based on a table. Could I filter the records of the form by only only including those records that are associated with the UserID of the user that just logged on?
The script: Private Sub cmdLogin_Click()
'Check to see if data is entered into the UserName combo box
If IsNull(Me.cboEmployee) Or Me.cboEmployee = "" Then MsgBox "You must enter a User Name.", vbOKOnly, "Required Data" Me.cboEmployee.SetFocus Exit Sub End If
'Check to see if data is entered into the password box
If IsNull(Me.txtPassword) Or Me.txtPassword = "" Then MsgBox "You must enter a Password.", vbOKOnly, "Required Data" Me.txtPassword.SetFocus Exit Sub End If
'Check value of password in tblEmployees to see if this 'matches value chosen in combo box
If Me.txtPassword.Value = DLookup("strEmpPassword", "tblEmployees", _ "[lngEmpID]=" & Me.cboEmployee.Value) Then
lngEmpID = Me.cboEmployee.Value
'Close logon form and open splash screen
DoCmd.Close acForm, "frmLogon", acSaveNo DoCmd.OpenForm "frmMain" Perhapt a filter here?????
Else MsgBox "Password Invalid. Please Try Again", vbOKOnly, "Invalid Entry!" Me.txtPassword.SetFocus End If
'If User Enters incorrect password 3 times database will shutdown
intLogonAttempts = intLogonAttempts + 1 If intLogonAttempts > 3 Then MsgBox "You do not have access to this database.Please contact admin.", _ vbCritical, "Restricted Access!" Application.Quit End If
