Adding One Field Of New Record Automically According To The Condition
Feb 21, 2008
hi all,
I am doing one project using access. I 've made two forms. In one form, lets say, it contains two common buttons. Both buttons will load the same second form and will add new record to the same table. When I click first button, it will load second form with all fields blank and one field of record will autofill one value,lets say "a" to the table.That field shouldn't be appeared in the second form,just want to fill automatically. Then the other fields of new records will be filled by the user input from the second form.
Also, when I click second button, it will do similarly but only the autofill value will be different from the first one.
Does anyone know how to make it?
If don't understand what I am saying, I can explain it again.
Any help will be grateful for me.
I wrote code that should validate a field when entering a new record and then if a condition is true, that new record should be cancelled and not entered into the table.
I managed to partially achieve this by writing the code below, but the new record does not get cancelled because the table will still create a PK for that record and leave the rest of the fields empty. I am using an autonumber for the PK that's why the table creates it automatically What I want to achieve is to cancel the creation of a new record at once, I don't want even PK created for that new record.
I used the CancelUpdate because I thought it would cancel the record creation, but it did not! When I read about it it said that I need to use it with either Edit or AddNew, (which i don't understand why!) but it still does not work.
Private Sub PlotNum_BeforeUpdate(Cancel As Integer) On Error GoTo Err_msg Dim db As DAO.Database, rs As DAO.Recordset Dim n As Integer, i As Integer Dim vPlotNum As Integer Dim vPhaseID As Integer
Set db = CurrentDb Set rs = db.OpenRecordset("tblHouse") rs.MoveLast n = rs.RecordCount rs.MoveFirst If n > 0 Then For i = 1 To n If rs![PhaseID] = vPhaseID Then If rs![PlotNum] = vPlotNum Then rs.Edit rs.CancelUpdate MsgBox "This plot number already exist in this particular phase." & vbCrLf & "Please choose a different Plot Number" Forms![frmHouse].qryHouse2.Form![PlotNum].Text = "" End If End If rs.MoveNext Next i End If rs.Close db.Close Set db = Nothing Set rs = Nothing
Exit_Err_msg: Exit Sub
Err_msg: MsgBox Err.Description Resume Exit_Err_msg End Sub
Any suggestions will be very much appreciated. Thanks. B
I need for a valued to be changed in a row after it checks for how many rows have another value, counting how many and that number making it the amount field.
Example: Fields- Name sponsor amount
I want access to show how many people have been sponsored by Gabriel... So if 3 where sponsored by Gabriel show 3 to the amount field for Gabriel row! Any way to do this or an easier way?
I for it to check it every time a user is added incase the user added is sponsored by Gabriel add it automatically!
Access 2002 . Can I condition a field to 'locked' on just one record of a continuous form subfile, based on the contents of a 2nd field in same record?
What I am trying to create is a Despatch database for our warehouse.
Records in table: Date () Customer Invoice # Qty of parcels sent Courier used consignment number
In most cases we will send one invoice per consignment number (database works fine for this) But on occasion we may send multiple invoices. What I want to be able to do is have a list box to select the number of invoices, this will make available additional fields for Invoice # and Qty of parcels sent. The idea is to get away from keying in the other records for each invoice going to the same place.
I am using a form to add a record to a table and need to be able to specify one of the fields in the table that will be added to. This field is predetermined by another form selection. If there is a way to force a value on a title box with a control source this would also do the trick.
I have a table that has records added to it using the following VBa code:
Const MyTable As String = "tblSampleSubmission" Const MyField As String = "SampleName" Dim db As DAO.Database Dim rs As DAO.Recordset Dim intCounter As Double Dim LastDub As Double Dim addString As String Set db = CurrentDb Set rs = db.OpenRecordset(MyTable) Randomize 'LastDub = Me.txtStartValue - Was only used to start the random function later in series addString = "" For intCounter = Me.TxtStartValue To Me.txtEndValue rs.AddNew rs.Fields(MyField) = Me.SamPre & intCounter & Me.SamSuf & addString rs.Fields("SubmissionNumber") = Me.SubNum rs.Fields("CustomerID") = Me.CustomerID rs.Fields("SamplePrep") = Me.SamplePrep rs.Fields("Fusion") = Me.Fusion rs.Fields("XRF") = Me.XRF rs.Fields("LOI") = Me.LOI rs.Fields("Sizing") = Me.Sizing rs.Fields("Moisture") = Me.Moisture rs.Update addString = "" If Rnd < 0.02 Then 'LastDub = intCounter intCounter = intCounter - 1 addString = " DUP" End If Next intCounter rs.Close db.Close Set rs = Nothing Set db = Nothing
What I would hope to be able to do is add a "standard" randomly to each SubmssionNumber (each SubmissionNumber might be 1-100 records). The record I need to add should be chosen at random from a list of 6 or so options and added at the end or middle or start of the job (SubmissionNumber) is this something that is easy to do or should I just give up and add it manually?
Thanks to everyone who has helped me in the past, it is getting me up to speed quickly. Access seems to be quite popular as I have contacted 3 developers to help with my dB but they are all to busy to help me so I am going it alone.
I have a text field "Record Last Updated" on a form formatted for date/time that I would like to update after a record is changed or added. So for every change or addition the field would update to the current date. The code I am currently trying to use is as follows:
'Assign current system date to Last Updated field if change of data occurs in any field For Each ctl In Me.Form.Controls
If (ctl.ControlType = acTextBox) Or (ctl.ControlType = acComboBox) _ Or (ctl.ControlType = acListBox) Then If Nz(ctl, "") = ctl.OldValue Then
Else txtLastUpdated.Value = Date End If End If Next ctl
This executes in the forms After_Update event procedure. Problem is I get an error 3020 "Update or CancelUpdate without AddNew or Edit" when moving to the next record ? I have tried using .Edit and .Update but those come up as an invalid reference? Any suggestions would be appreciated. Thanks in advance
I have a form based on a mysql table. There is a button in the footer to add a new record.
The pertinent vba code: DoCmd.GoToRecord , , acNewRec
It adds the new record and properly places the cursor in the first field. Immediately after the first letter is typed, the error message "Field cannot be updated" pops up. I can click ok and the message goes away and I am able to continue filling in the fields. The same thing happens if I add the record by use of the record selectors.
how to automatically populate a certain field. To add some context, I have a form which registers the details of a contact with standard information of contact details. There is a subform which shows the different products that the client from the main form is interested in. This is a actually a data sheet which returns the results of a query (selecting from the relevant table the client in question and the products he/she wants).
I have added a button which opens up another form and allows a product (and hence a new record) to be added for that particular client. I would like that the form automatically populates one of the fields in the form that is the client id. Given that the subform is opened from a form which already identifies the client, how do I do this?
I have a table A in which I write down orders for cars. A record is an order. A single order may contain multiple cars in varying quantities.Each car has its components. Some cars may have some of their components common. There is a table B which indicates each car and its components required with their quantities required to build the car. There is a record for each different car.
Now suppose there is a new car we are going to produce so we need a new record in table B for the car and all its components. Also we need a new field in table A because people can now order the new car(in some quantity).
With form for table B we can introduce a new record. But how can we add a field in table A automatically after a record is added in table B?
What method(s) are available to detect when a user is at a new record? I really just want to change the .text property of a combo box when the user is creating a new record. If this sounds stupid, I can explain the specifics.
I have a form for entry and some fields are computed or result of a query from another table. I have a function that looks up a value from another table like so
************************************************** ******** Public Function GetTargetType() As Variant GetTargetType = DLookup("type", "tblFormulations", "[tblFormulations!formulation]=Forms![frmNmsConsumptionEntry]![formulation]") End Function ************************************************** ********
Which works fine when I test in the immediate window.Then I have this form event. This however does not insert this value when I am adding records using my continuous form.
************************************************** ******** Private Sub Form_BeforeInsert(Cancel As Integer) Me!target_group = GetTargetType() 'Forms!frmNmsConsumptionEntry!target_group = GetTargetType() '[tblNmsConsumption.target_group] = GetTargetType() End Sub ************************************************** ********
making sure I can insert this value once retrieved.
I am building a database where one Form displays records from 14 different tables. For some reason, when I recently try to add a field on to a form from a new table, the ENTIRE form loses the record source, and every single field that is already on the form gets that green dot in the corner with errors surrounding a record source that cannot be found. What am I doing wrong? Am I exceeding some limitations with forms?
Hi. I am using Access 97. I have a problem with multi-conditional field showing or not.
Specifically: I have 2 fields which can either be null, 0, or have a numeric value. These need to stay invisible to staff. There are 2 other fields, also invisble in their properties setting, which need to become visible if both of the previous fields do not conain a value greater than or equal to 1. Because these 4 fields are invisible by default, I put the following code in the settings for the 1st visible field in the form. I also tried it with when the form opens. But it doesn't work or I get debug problems.
If (TOTAL___WAGE_RECORDS_E = 0) And (TOTAL___WAGE_RECORDS_E Is Null) And (TOTAL___WAGE_RECORDS_E_OS = 0) And (TOTAL___WAGE_RECORDS_E_OS Is Null) Then Me!EMP_QUART_AFTER_EXIT_QUART.Visible = True Me!EMP_QUART_AFTER_EXIT_QUART.Dropdown End If
condition formatting a date field in access.The query used for this field produces a date or "NA" based on the formula below. so when the date shows up as "NA" then i have set the condition formatting of the cell to grey and this works works well. but when it shows up with a date it doesnt format to a grey.the formatting pane has these two expressions
1. Q_Induction_Date = "NA" then grey the cell
2. Q_Induction_Date < DATE() then grey the cell and this DOESNT work. i have also tried the function Now() and that doesnt work either.
I have a report which I would like to change the text of a field blue if a certain condition is met. What I want to happen on this report is if a specific field has an "Active" - then it will be in Blue text, otherwise it is in black text.
I have gone into the report ->in the Details section -> put a procedure in the On Format event. The code I have been trying is:
----- If Analysis_Status = Active Then Me.Analysis_Status.ForeColor = vbBlue Else Me.Analysis_Status.ForeColor = vbBlack End If End Sub ----
I've a quick question. In Access, I have a form that allows user to add new record into a table. Is there anyway of finding whether the new record has successfully been inserted or added?
So i think it may be wise to have a message to notify us of whether it's inserted successfully or not.
Can someone please tell me why I might be adding a record everytime I open my database. I have to forms that work from querys if I open the main form all is well , if I open the other oone first and then the main form I find a record has been added. Most annoying! :(
I have a table of vacinations and need to add a new record every time an animal is vacinated. I have created a form with all the fields in the table in it. But when I open it it takes me to the first record. I have had to add a button to the form to add a new record. This button then takes me to a blank form where i can input data. This is very messy and not very user friendly. Any suggestions on how i can tidy this up would be appreciated greatly.
I have looked on here for hours and cannot find anything, perhaps someone can help. I have just converted from 97 to 2003 and now I cannot add new records to my forms. I have checked that the allowAddtions property is set to Yes but the New record icon and menu option is grayed out. I have looked through all the form settings and I can't see what it might be.
I have a form that has a list of textboxes linked to table properties, and a subform linked to the same table. I used the command button wizard to create an add record command, but when i click on it it comes up with:
You cant go to the specified record You may be at the end of a record set
i have checked the properties on the forms and querys and set all data entry to yes What should i do?
