Modules & VBA :: Retrieve / Validate Data On 2nd Form Based On Record Selected On 1st Form
Jul 29, 2013
Can I look up and verify data on a "second" form based on a selected record from first (still open) form.
I am trying to allow users to select a User Name from a combo box list and then open "Change Password" form when they select "Change Password" for that selected user name.
My problem is that I can't figure out how to associate and verify the data tied to the user name selected on the previous (Login) form ( I am trying to validate the old password tied to that selected record).
I have the first login form created, and it's working just fine. I also have the change password form created (and it's displaying the user name selected from the first form using:
Code:
Private Sub Form_Load()
With Forms![frmLogin]![cboUserName]
Me.txtPwdChgUserID = .Column(2, .ListIndex)
End With
EndSub
I also have the code written to validate and confirm old password, new password and validate new password (when the save button is clicked). I have yet to update the password with the new password (still trying to figure that out).
Attached zip file has screen shots of the two forms.
I am trying to validate that the user selects at least one checkbox out of three on the form in Access 2010. They can select one, two or even all three if they wish, but at least one must be checked. I am using the following code attached to the click event of a command button. It was fine but seems to have stopped working. The control names are generic to illustrate my code. The form is unbound:
if (chk1 + chk2 + chk3) = 0 then Msgbox "Please select at least one checkbox" exit sub end if
I was wondering if anybody could help with this. I have searched through this forum and there doesnt seem to be an answer to this.
I have a form that i would like to validate. At present i have put the data validation on the save button which triggers a script that checks to see if certain boxes have been filled in on the form. I can only get this to work be attaching it to the onclick event on a save button, however, what i really want is to be able to ditch the save button (since access writes to the DB as it goes along) and have this script triggered whenever the user tries to navigate to either a new record/ another existing record or close the form
I tried putting this on the beforer update event, but this does not work, additionally tried doing before update event with the code inside an if me.form.dirty = true statement this did not work either, has anybody got any suggestions
Please see my code below
Thanks
Marcus
Dim sDeliveryto As String Dim sDeliveryValid As String Dim sDept As String Dim sDeptValid As String Dim sReq As String Dim sReqValid As String Dim sReqNo As String Dim sReqNoValid As String Dim sReqPoint As String Dim sReqPointValid As String Dim sOrderDetailsValid As String Dim sQuantity1 As String Dim sQuantity1Valid As String Dim sDetails1 As String Dim sDetails1Valid As String Dim sPrice1 As String Dim sPrice1Valid As String Dim sSupplier1 As String Dim sSupplier1Valid As String Dim sCostCentre1 As String Dim sCostCentre1Valid As String Dim sAccountCode1 As String Dim sAccountCode1Valid As String Dim sAuth As String Dim sAuthValid As String
If sDetails1Valid = "Invalid" Or sQuantity1Valid = "invalid" Or sReqPointValid = "Invalid" Or sReqNoValid = "Invalid" Or sReqValid = "Invalid" Or sDeptValid = "Invalid" Or sDeliveryValid = "Invalid" Or sPrice1Valid = "Invalid" Or sCostCentre1Valid = "Invalid" Or sAccountCode1Valid = "Invalid" Or sAuthValid = "Invalid" Then MsgBox "Please fill all highlighted fields on the form!!!!!" Else
DoCmd.Save MsgBox "Is all the information Correct?", vbOKCancel
I manage an Access application that in many instances uses data selected from a combo on a form for variable criteria. In this instance it is in the form of:
I recently wrote a routing that exports to Excel based on a record set derived from a query. In testing I hard-coded the criteria (School Year) in the query. Once everything worked I sustituted the variable above. Now, in the VBA, no records are put into the recordset, when I run the queries directly from Access they work correctly, drawing the results for the school year selected on the form referenced.
'Create The Recordset If Me.Frame11 = 1 Then strQueryName = "ExcelHS" GroupTitle = "High School" Else strQueryName = "ExcelMS" GroupTitle = "Middle School" End If Set objRst = Application.CurrentDb.OpenRecordset(strQueryName)
Private Sub ChargeReport_Click() On Error GoTo Err_ChargeReport_Click Dim stDocName As String stDocName = "Charges_Report" DoCmd.SendObject acReport, stDocName, acFormatPDF, , , , "Charge Sheet"
[Code] ....
Currently, this code opens an input box that accepts the ID number for a particular record. Then it attaches a specific report for the selected record to an email. It works fine for this purpose.
I want to modify it so that it attaches one of several different reports depending upon the value of a [Staff_ID] field in the selected record.
I've tried a number of different solutions using an InputBox to get the record ID along with an If/ElseIf/Else construct that evaluates the [Staff_ID] field in order to determine which report to attach to the email, but I cannot find my error.
Is it possible, to input information from my continuous form into a web control form. at my job we a required to tract our jobs by equipmentid and job control number(jcn). When a job is done we have to upload the id and jcn into a website to tell it is cleared. I am looking for a way for that info to automatically be filled in when i load the website based on the job i have selected in my form?
I have a table called 'Klanten' which contains the rows 'password' and 'login' (and several rows not needed for this form)
So I'm trying to make a login form which first checks if something is entered (this part of the code seems to work).
Private Sub Knop13_Click() 'Check to see if data is entered into Username If IsNull(Me.Username) Or Me.Username = "" Then MsgBox "gelieve een login in te voeren.", vbOKOnly, "Required Data" Me.Username.SetFocus
[Code] ....
But from then on i seem to have some issues.. The part of the code underneath seems to only work for the first 'login' and 'paswoord' in my table called "Klanten".
-Username is the name for the field where they enter their 'login'. -Password is the name for the field where they enter their 'paswoord'
If Username.Value <> DLookup("[login]", "Klanten", "[Username]='" & Username & "'") Then MsgBox "Invalid Username. Please try again.", vbOKOnly, "Invalid Entry!" Exit Sub End If
If Password.Value <> DLookup("[wachtwoord]", "Klanten", "[Password]='" & Password & "'") Then MsgBox "Invalid Password. Please try again.", vbOKOnly, "Invalid Entry!" Exit Sub End If
Then as last part i would like to goto another form called 'Mainmenu' if both the Login and the Paswoord is correctly entered in the fields Username and Password. Here i have the most issues as this doesn't seem to do anything at the moment
If Password.Value = DLookup("[wachtwoord]", "Klanten", "[Username]='" & Username & "'") And Username.Value <> DLookup("[login]", "Klanten", "[Password]='" & Password & "'") Then DoCmd.OpenForm "Mainmenu" End Sub
I have code that is executed with the click of a button to enter a new response using 2 fields to differentiate the records: a combobox "cboSrvID" and a textbox "RspnsName". I have a different set of questions for each value in the cboSrvID. Upon selecting my button the record is saved correctly although I would like it to open the blank form associated on the subform "sfrmSurveyResponses" when pushed. I have tried some DoCmd.FindRecord and DoCmd. GoTo functions to try and retrieve the last acLast RspnsID inputted. So far I have had no luck.
Below is the code for the onClick action of my button.
Code: Private Sub cmdEnterResults_Click() DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70 DoCmd.SetWarnings False DoCmd.OpenQuery "qappNewResponses" Me![sfrmResponses].Requery End Sub
I am attempting to insert a record with selected data into a temp table and I am getting "Run-time error '3075': Syntax error in (comma)...". Here is the code:
Code: Private Sub XferDataToTempTable() Dim db As Database Dim strSQL As String Set db = CurrentDb
And I want to extract the "Submit Date" data and place them into their corresponding fields on a form:
Basically I want to take the Submit Date column from the table and place them (in the same order) in the form. How can I do so? I'm also confused as to what to select for the Control Source in the Form Design.
*Note: both "Submit Date" columns in the form and table are set to Date/Time.
I would like passing values from first form until third form.
In the first form I have a list box after selecting items (For each selected item in first form I have 4 values) and pressing button (or right click of mouse) the second form will be open, then in the second form I have 2 option (inserting, deleting), when I select inserting or deleting in the second form, third form will be open, in the third form there is a "OK" button, when I press that, passed values from first form will be used for inserting or deleting records to the table.
i have a form based on a table.The key column of the table is a combo box in the form , if i select a particular value in the combo box then i have to diaplay the all the fields in the record on the form automatically based on that particular value.
Ofcourse it is a simple task, but today only i am trying my hand in VB for the first time in my life.... so i find it difficult to find the answer.......
I have a form named CustomerForm.I have a query named CustomerQ. On my form I have a combo box named combo6.Combo 6 lists all of the company names from my customer table and includes the autokey field which is hidden.When I click on a value in my combo6 I want the values on my form to then be based on the value from combo6 whereas at the moment I select a value in combo6 and nothing happens other than combo6 now displays a different value. how to refresh/ repopulate the data based on combo6 without having to create more forms and queries.
I've search around the forum and haven't come up with the answer yet.
So here goes...
I have a main form (GRCSearch) that is tied to a table (StoredSearches). When users enter data in this form it saves it to the table. It is a single record form. The data entered by the user is used to search a big second unbound table via some queries.
The search data entered is then recorded to the table (StoredSearches) as a way for the user to track searches and to recall them if they want to run the same search again.
I've created a pop-up form that displays a continuous record layout of the StoredSearches table and a command button at the left of each record.
I want the user to be able to click the command button to the left of each record in the pop-up and that in turn set the focus of the main form to that record. It recalls the search data, repopulating the search fields of the main form. And in the onclick event I'll also have it close the pop-up window.
I however cannot find any references to setting the focus from one form to another form this way. I've found plenty that changes the focus of a form from an embedded subform.
I have a form with a subform in a navigation menu. Something like:
Navigation Form NavigationSubform ParentForm (header data and some unbound calculation fields) Subform (Multiple lines tied to query)
I have some fields in the ParentForm (i.e PF1, PF2) that would effect the values on the Subform (i.e. SF1, SF2). Also, there are some user editable values on the subform that will calculate the remaining fields on the subform (still using data from parentform).
My calculations work fine for the changes made on the subform. However I need to be able to calculate all the children lines on the subform when the form loads or a change is made to certain fields on the parentform. My On_Current event only wants to recalculate the first line.
Parent Form PF1=10 PF2=3 Subform QTY ADJ SF1 SF2 Line 1 5 .05 10.6 112.89 Line 2 8 .14 10.38 105.15 ... Line N SF1=PF1+(PF2/QTY) SF2=SF1*(SF1+ADJ)
So if PF1 or PF2 were changed then all the lines is subform would recalc SF1 and SF2. If changes were made to QTY or ADJ, then that line would recalc SF1 and SF2.
I have a modal form which has a single record which is then linked to a sub-form on the same modal form. This all works fine and shows the relevant record and sub records but I want the modal form main record to change dependant on the record selected in the subform (which is basically order item history.
I found some code on another site which seemed to work for others but for me I am getting Run-time error 2465 and it doesn't like my reference to 'Me' - is this because it is a modal form?The code I am using is:
Public Function GotoRecord(RecordID As Long) Dim rst As DAO.Recordset Dim strCriteria As String Set rst = Me.RecordsetClone strCriteria = "ID = " & RecordID rst.FindFirst strCriteria If rst.NoMatch = False Then Me.Bookmark = rst.Bookmark
I've been able to navigate a lot on my own, but there is one issue I cannot seem to resolve. I have 1 form and 1 table. I have the form set up so that you can enter data, and then press a button, and it will "save" and refresh the form for a new entry. However, I want to be able to pull that entry back up in the form, and fill out additional fields later on.The form is set to data entry = yes because I do want the form to open up as brand new each time.
To sum up my question. I want a text box and search button at the top of my form. When you type an ID number in the text box, and then press search, I want access to populate my form with the information in my data table associated with that ID number.
If I type in the number 1234 and hit search. I want my form to autopopulate with the data in the row for ID number 1234 (all the fields I have already populated). So by searching 1234, the name, phone, background info, etc that is populated in the row will appear.
I am new to access and I recently encountered a double click issue
My form loads perfectly on double click event but it shows the first record instead of selected record.
My search is based on a PersonID but each PersonID has different WorkID that I wish to display on double click but it always shows the first WorkID instead of my selected record
I have tried changing the filters in the form properties but it still doesn't work for me.
Here's my code:
Private Sub SearchResults_DblClick(Cancel As Integer) DoCmd.OpenForm "WorkForm", , , "[PersonID]=" & Me.[Searchresults], , acNormal End Sub
[Searchresults] draws information from my Query
Query information:
PersonID... WorkID... Type......Location 1234..........1............Paint .....Address A 1234..........2............Electric...Address B 1234..........3............Floor..... Address C
I have a split form that was not made by wizard. On the form part I have a combobox that is unbound to the form data set. The combobox has a query row source that is based on the current row selected. I want the combobox to have an up-to-date result based on which row is selected.
If I set the combobox to requery in the form_current event then I get what I want. I don't want to put up with the little delay that is generated every time a user changes rows because of the requery, though. I only want the requery to happen when they use the drop-down menu.
I have the requery in the gotfocus event of the combobox on the form. I mostly get what I want this way, however if they select an item in the drop-down list, then choose a new record in the datasheet, then try to use the combobox again, the combobox is not refreshed (because it never lost focus?).
To get around this, I've tried to setfocus to something on the form in the on_current event, but access gives me an error: 'Access can't move focus to the control btn_Refresh'.How can I get the combo to requery only when users are about to use it?
I have a form that is filter based on a combo box. I would like to add another filter for date. but the code I'm using for the first combo box doesn't work for date.
the code is:
Sub SetFilter() Dim LSQL As String LSQL = "select * from Preventive_Q_View" LSQL = LSQL & " where Item_Name = '" & Combo206 & "'" Form_Preventive_View.RecordSource = LSQL End Sub
How do I modify this code to work with the date combo box? Also, is there a way to get both filters to work together, as in filter based on the first combo OR the second combo, OR both?
I'm trying to add data about a form to a table when the On_Current event fires. The data I want to add is: windows login username
date and time the On_Current event occurred name of the form that was opened that the form was opened the number of the record (ID) in the table the form is based on
I have the following code
Private Sub Form_Current() If Me.NewRecord Then Call FlimOpen("OR IR No", "OPENED") Else Call FlimOpen("OR IR No", "OPENED")
[Code] ....
Excuse the use of 'FlimOpen' but I needed a unique word that I would remember (inspired by FlimFlam the cat on the children's channel CBeebies here in the UK)
I try to compile and I get an error about the wrong number of arguments or invalid property assignment. However, the event does fire correctly when I open the form or move from one record to another so something's working!
I have a form that updates records. When the user opens the form in data entry mode, it is too difficult to find the correct record. What is the most efficient and simple way to have the form open to a specific record?
Solutions I have considered are a popup form that the user will enter the equipment code (which is the unique identifier of all records). Also, the user may not know the record number, but they will always know the equipment code (which is a field in the "equipment" table).
But I am not sure of the best way/best order to do this.
I have a form and subform where i have table data in the subform and 03 Combo box in the main form header. I need to do some filtering using combox box1 and out of that filtered records i need to do one more filtering using combobox2. then again another filtering by Combobox3. ( same way we are doing in Excel)
Subform is running on a query where i have following fields;
Vessel Voyage, POL POD MLO
by Combo box 1 i need to filter Voyage by Combo box 2 i need to filter POL ( out of the data filtered by above ) by combo box 3 i need to filter POD ( out of the data filtered by both above )
I've got a data entry form bound to one table. The form has four buttons:
- Clear Fields - Cancel - Save and exit - Save and add another (which should save the user input to the subform/table, clear the input fields, and allow the user to add another record)
I can't quite seem to get the "Save and add another" button to work. When I put some information in the input fields and click the button, it saves it to the subform/table perfectly, but when I try to do it again, it just edits the last record (the one just created).
How can I get that button to place the information from the input fields in a new record every time?
The _Click event for the button looks like this:
Code: If Len(Me.field1 & Me.field2 & Me.field3) > 0 Then Me.Refresh btnClear_Click DoCmd.Save End If
This works great to return a report if the user selects something from the combo box. How do I adapt this so that the user can also leave the combo box blank and filter the report to return all records?Additionally, what if I want to have the user filter between dates selected on the form; i.e. between 'txtStart' and 'txtEnd'
