Using The Current Value Of A Field, Before It Is Saved
Sep 8, 2005
Hi all,
I've got a subform, fed by a query which contains a lot of calculated fields. All the calculations use the contents of a field on the main form.
What i want to do is have a live update, so that if i change the value of the field on the main form, the subform is requeried and the fields recalculated using the new value.
I've put a subform requery on the On Change event, but the requeried fields are coming out the same, presumably because the new value i have put in is not saved until i change record.
I'm using the control name in my calculations, and yet it's still picking up the old (saved) value from the underlying table - is there no way to tell Access to look at what resides in the control right now?
What i want to do is if a user forgets to put lets say Address or Zip Will not allow user to save record but instead will highlight the text boxs that are required. Once data is put in will allow user to save record.
How can I force a field in a form to be updated before the record is saved / changed?
For instance I have a form with information on it and I want to ensure that any time the form is updated the user fills in a section providing the date and by who it has been updated by. I dont want the record to save unless that information has been filled out, and I also want it to take you to the field if you press save and it hasnt been filled out along with an error message.
To try and be a bit clearer. At current I have a Save and New button. This saves the form if dirty and opens a new record.
I want to add in the step that if record has been changed and FieldA has not just been updated then go to fieldA (Showing a message box). If FieldA has just been updated then save record and open new as normal.
My current save & new button properties are as follows (in Macro Editor)
On error Go To Next If [form].[dirty] RunMenuCommand Command SaveRecord End If If [MacroError].[Number}< >0 Message =[MacroError].[Description] Beep Yes Type None Stop macro On Error Go To Fail Go To Record Record New Go To Control Control name Resort Code
I have designed a database that has two forms as inputs to a table. The first form is a checklist and when it is completed it saves all fields except the ManagerID field. I then use the blank ManagerID, clientID and Date to pull onto a form for the manager to complete. On completion I want the ManagerID to save into the current records so they do not show up in the manager checklist forms and I then have a complete record. I have been searching online and cant seem to see how the best avenue is. I have an append query, see below
Code: INSERT INTO ChecklistResults ( ManagerID ) SELECT ChecklistResults.ManagerID, ChecklistResults.ClientID, ChecklistResults.DateCompleted FROM ChecklistResults WHERE (((ChecklistResults.ClientID)=[Forms]![TeamLeader]![ComClientNotFin]) AND ((ChecklistResults.DateCompleted)=[Forms]![TeamLeader]![ComDateSelect]));
Code: Private Sub CmdAppend_Click() Dim dbsNorthwind As dao.Database Dim rstAmend As dao.Recordset Dim qdfAmend As dao.QueryDef
A have a query that selects a multivalue field. The query forms the record source for a subform. The text values in the multivalue field are displayed in the query, but when I save this and view the data in the subform datasheet view the values revert to the primary key values. They are 1,2,3,4 instead of the text values
i have 4 supplier tables with identical field names but different databases in sql.I want to have them all in one table and only want the information for reference i do not want to edit any of the data.I need to create a new primary key number for the complete table. The data will need to be refreshed as the data comes from MMS Sage looking at company PLsuppliers.
I have tried a linked SQL union view- but this has no primary key.I need to keep the Primary key consistent as the data saved in other tables will use this field.The overall goal is creating a Purchase order system and this list will be my complete supplier list.
I was wondering if this was possible. I would like to have a field on my form that states who last saved the entry.
I have a table that has business prospects and people responsible for them. A certain entry my have 2 or 3 people responsible for the prospect. I wanted to record who last edited a particular entry, is this possible?
Oil quantity Accumulate Oil quantity Date Recordnumber
I want to get value of "Accumulate oil quantity" of current record to be equal to sum of "Oil quantity" from first record till the current record:
Let say after I input "oil quantity" in the record # 1 ,2 & 3 The "Accumulate oil quantity" of record value show below:
"Accumulate oil quantity" of record#1 = "Oil quantity" of record#1 "Accumulate oil quantity" of record#2 = "Oil quantity" of record#1 + "Oil quantity" of record#2 "Accumulate oil quantity" of record#3 = "Oil quantity" of record#1 + "Oil quantity" of record#2+"Oil quantity" of record#3
I have a form with date/time field. I have the default value at =Now(). My problem is I would like for this particular field to stay with the current date/time all the time. I mean if I created a form last week with the date/time of 3/14/2005 12:54:34 PM and go back to the same form to edit something this week I would like for that particular date/time to be the current date/time and not the date/time from the week and time the original form was created.
Hi Guys, my first post hereim making a database for work and need to figure out the followingi have two people that uses a ms access db, each entering data.i made a custom macro in the vb editor:Public Function cUsername() cUsername = Environ("USERNAME")End Functionwhat i want to do now is, everytime a new entry is added in the table, it should add that username to the "user" cell... it should only do this once, so for example, when i enter data it will show "albert" and if i was to close it, login as "steve" it should still show "albert" under the ones i added, and not steve...this needs to be in the table not a query or form...thanks for your timeAG
So I have a control called txtBranch. If the user does not put 1 of 4 options then a msgbox pops up and says that is an invalid option and then gives all the valid options. It then sets the value to blank. Then it moves to the next field. I do not want it to move to the next field. I want it to stay on txtBRanch till it has a correct value. I have tried DoCmd.GoToControl "txtBranch", Me.txtBranch.SetFocus on the OnExit, OnLostFocus, AfterUpdate events and it still continues to go to the next control how do I get it not to go to the next control?
Adding a second auto number field to current table and auto numbering current entries?
I need to combine some records from the same order number that currently have detailed names. I'm trying to simplify them, but I can't because it creates duplicate records.
I am trying to resolve an issue of being able to use a query from many different forms where the query is dependent on the date selected in the current open form. Right now it calls the function getDate() which works fine. Unfortunatly the function is called before the form is fully loaded and the control I want to pass has no value/doesn't exist yet and I get a "Runtime error 13 Type-Mismatch"
Here is the function code:
Function getDate() As String
If fIsLoaded("F_SupplierData") Then getDate = Form_F_SupplierData.txtDate Else getDate = "01/01/1901" End If
End Function
Function fIsLoaded(ByVal strFormName As String) As Integer 'Returns a 0 if form is not open or a -1 if Open If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <> 0 Then If Forms(strFormName).CurrentView <> 0 Then fIsLoaded = True End If End If End Function
Is there a way to detect the state of the text field itself (if it is open, closed, dirty)? Or if anyone has a better way to tie a query to multiple forms?
Does anyone know how to automatically enter the current date into a specified field by simply clicking on a check box. I imagine the IIf function would work when entered into 'Control Source' but I also need the check box to be linked to the underlying table. i.e. I tick the 'Paid' checkbox and the current date is automatically entered into the 'Date Sold' field. I also need the -1 generated by clicking on the checkbox to be stored in the table.
Any suggestions would be appreciated, although I am no good with code, so if you could post an example or an easier way to do it I would be most grateful
Having a bit a brain freeze today. I have a field that auotmatically puts a date in when a checkbox is checked I am now trying to add a field which returns the day of the week from this date. Brain now mashed I am sure it's pretty simple but I just can't get it to work
Every week i want to import a csv-file (250 records) which may or may not have been updated, say 200 are the same as previous week and 50 records are new/updated.
Is there a way to import the csv and add an extra column with who imported all of the new records (based on currentuser)?
I am trying to record the current windows username in an audit field. But all i can get is the Username Admin?
Dim MyForm As Form, C As Control, xName As String Set MyForm = Screen.ActiveForm 'Set date and current user if form has been updated. MyForm!Audit = MyForm!Audit & Chr(13) & Chr(10) & _ "Changes made on " & Now & Date & " by " & CurrentUser() & ";"
I am trying to figure out to have the current date populate in table field by using a button on a form and not having much luck.
I have a table with 2 date fields, a 'created' field and a 'received' field. I already have the date set to auto populate for the 'created' field but I don't want the 'received' field to populate until the employee has gone into the database to receive the work.
I know I could have the employee just enter the date, but i want to avoid any typos or people simply forgetting to do it.
Ideally I would love the button to enter the current date into the 'received' field and save the entry, but I fine it needs to be 2 seperate buttons.
What I really need is for when the form opens, it looks at todays date, then matches current user and then goes to that record for today, if no current user there, then will goto new record..
i know, sounds complicated, and probably is really easy, but my heads not with it today, as about to get drunk as its my 40th, and got people ringing and texting and still trying to get this done....
I've included a copy of this database, named Timecards..
