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!
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
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.
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 ----
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?
Ok, this may be a ridiculous question to ask, and i imagine there is a simple answer, but i cant see it. maybe its coz i learnt access from the "for dummies" guide :)
i have a table with a "quantity" field. i have a form for finding a record, and then i want to enter a value in a box and press a button to add it to the existing value in the "quantity" field. basically it is for increasing the amount of a certain type of item in stock when a new delivery comes in.
Please help me, im a simple man and havent got a clue
I got a question about a database I'm trying to design. It's a small cable management system.
I want to create a form that shows where the port of a computer is connected to.
For example; A computer has one ethernet port. That port is connected to a switch. I can add the fields for the Unique hardware ID and for the ports of the computer. But I can't seem to chose a device where the computer is connected to.
Ive tried working with queries.. but nothing seems to work so far.
I created a small example of the database I'm trying to make.
The attachment is the database Im working on and I have a problem. I need to add a supplier Product number in there some where so I could keep the numbe as reference and so it may be easier to enter items recieved. in the form section, I will have Invoice with a sub form Invoice details. I need to put suppliers Item number somehow so it would search and if the item is on the product list it would fill out the rest of the values.
should I make a separate table for suppliers product number or should I just include that into the invoice detail? I dont want to put it in the product table since I get same Items from different suppliers.
Hi again. I finally have time to start working on my access project, and what I want to know is how could a field technician add to access from the field, either with a laptop or possible even a pda. things a tech would need to add are notes, different readings that monitor job progress, equipment placed at the job site, other things like that.
I hope I can ask this question so that it makes sense.
I have a totals query that I'm using to make a Chart. The query works fine but I want to change the look abit. What I'm doing is quering a table looking for the total number of times that there is a check box in a field called PDC RTA and totaling the Hrs as well. The data comes back looking looking like this. PDC RTA-----Count-----Total Hrs -1 ---------- 6 -------- 53 0 -----------25 -------- 78
My problem is that when I chart this the -1 and 0 show up at the bottom of the bars on the chart. I would like to rename these to fields on the qry qrid then I can chart the new field names so that it will make sense to the user.
I have a field that contains various text....(It's a note field that allows 86 characters).
I want to go in to the database and add the string "Conversion 2007" to the end of every record. So leave whatever is in there but add in the string. I would like it to add whatever it will fit, in the cases where there is not enough room.
I figured this was somewhat simple, but I haven't found anything yet. Thanks.
I am using a Microsoft Access Form and would like to add some new fields and have no idea where to start. Any step-by-step help would be greatly appreciated.
I am an amateur and would need step by step so minute details would be greatly appreciated.
Can somebody help me with forms. I have not used Acecess for sometime and have created a form and realised that I had not included another field in the form. Can somebody help me I have forgotten how to add in the additional field. Simply please:eek:
Can somebody help me with forms. I have not used Acecess for sometime and have created a form and realised that I had not included another field in the form. Can somebody help me I have forgotten how to add in the additional field. Simply please:eek:
I have created a form and now realised that I have forgotten to put in a field. I have forgotten how to add an additional field to the form. Help in plain terms please. Thanks:eek:
I'm importing a text file into Access. The file is just a string of numbers that I'm breaking apart into three fields. Once those three fields have been broken apart by the import tool I need to add a new field that just contains the year.
For example, I import the 1990 data file that is .txt format and I break apart the three fields while it's imported. Once imported into three fields, I want to add a 4th field that says 1990 for every record. This has to be a pretty simple thing to do, but I can't figure out how to add a single number as a new field to every record in the database.
I'd like to add a customer type field from my 'Customer:Table' to my 'Customer Balance Report' Also, I need to create a validation rule in my table specific the legal values of SAL, SPG and WMN in my customer type field. with validation text, which I'm not sure how to do.
I have created the code below to add a new column to a table each month. This may not be the best database design but it meets our needs for now.
However I am having difficultly with the code below. The CreateField Function is unable to accept the parameter periodDate. Any Suggestions on this would be apprerciated
Function DateField() As Long
Dim colFullName As Object Dim dbsCurrent As Object Dim yearInt As Integer Dim monthInt As Integer Dim table1 As Object
Set dbsCurrent = CurrentDb Set table1 = CurrentDb.CreateTableDef("103TblCustomerBalancesCombined")
yearInt = Year(Date) monthInt = Month(Date) - 1
If monthInt = 0 Then periodDate = CLng(yearInt - 1 & 12) Exit Function End If
If monthInt < 10 Then periodDate = CLng(yearInt & "0" & monthInt) Else periodDate = CLng(yearInt & "" & monthInt) End If
Set colFullName = table1.CreateField(periodDate, DB_TEXT) table1.Fields.Append colFullName
1. I need to know how to update the field list of the query after adding a new field into the table and the query? 2. I need to know how to update the records table after adding calculated filed?
Can somebody help... I'm trying to put via an automatic way a field to a table that autonumbers. When I use a select-query: ALTER TABLE tablename ADD COLUMN Id Autonumber, Access doesn't recognizes the type "Autonumber". Even in VBA when I use the .createfield method, I can't set the datatype "Autonumber". I'm pretty sure it must be possible (as access can store anyway the indexes.. actually, I want those indexes as numbers in a separate field).
I am trying to add a certain number of days onto a date field to create a due date within a Table but can't work out how to do it. I know that to add days on I can use the function DateAdd but the only way I can see how to do this is to create and update query to run and add the date on. Is there anyway that I can set the field to automatically update the due date dependent on the priority of the record e.g. immediate (1 day) standard (3 days) and request (28 Days)?
