I am using MS Access 2010 and I am struggling to write some vba in my database. On my form, I have a Payment Type dropdown field.When someone selects a payment type I want the code to calculate the end date. Then populate the End Date field with the answer.I will be having a few payment types too, but for now on the dropdown I have two.2/Month and 24/Annum.
View RepliesI am trying create some code that checks if the contents of the status field in a table is "Authorised" where the ID = something specific for multiple records. If all records witht the specific ID are "Authorised" Then generate a new record in another table. Where to start, perhaps a Dlookup?
View 1 Replies View Relatedi've got a data entry form. when i click the button add all the other buttons go grey, i am in mode "New" also i am able to add, edit, records in this state, when i press save i want the supplierID field to automatically populate itself with the next SupplierID record THAT IS EMPTY. So say that my last record was ID number 45 then when i click add on my form and go into "New" state the SupplierID field automatically populates itself with "46" btw this is all an unbound form,
View 7 Replies View RelatedI would like a date field I have set up to auto populate the current date when I click on another field to enter information.. how would I do that?
View 5 Replies View RelatedI am trying to get the max of an integer field if the ProjectNumber field is equal to the ProjectNumber field in the current record. I have used code similar to the one below before but it was on a date field, then add one.
Me.RevisionNumber = Nz(DMax("[RevisionNumber]", "dbo_ProposalLog", "[ProjectNumber] =" & Me.ProjectNumber), 0) + 1
The ProjectNumber field is a text field.
I have a code that finds the first, second, and third minimum value in a row across the fields. Now, I am trying to find a code to look at these values, find the field it is located, and return the field name. I tried several variations of my code to return the field name rather than the value, but have been unsuccessful to this point.
Function NthMinimum(intPosition As Integer, ParamArray FieldArray() As Variant) As Variant
Dim varTempArray() As Variant, varTempValue As Variant, intArrayValues As Integer
Dim I As Integer, J As Integer
ReDim varTempArray(UBound(FieldArray))
intArrayValues = 0
[Code] ....
As you can see, this works to find these values while ignoring NULLS. How to return the field name?
I have a field within a form that needs to be modified based off of someone's access level and I have written that part but not sure how to keep others from modifying the field.
Code:
Private Sub Qty_Rcvd_Click()
If ap_GetUserName() = "Danny_Davis" Or ap_GetUserName() = "christopher_ayers" Or ap_GetUserName() = "Tena_McCrackin" Then
GoTo 10
Else
MsgBox "You are not authorized to view this form"
End If
10
End Sub
Right now this code will bring up the msgbox for someone not authorized but once they hit "ok" the msgbox goes away and they can still change the field. What can I type in here to lock down the field for someone not authorized?
convert the following into VBA code for an Access 2007 field on a form. It is needed to create a conditional default value based on another field's category: (Note: TransType, MembershipYear and Dues are field names and are all on the form). Based on the TransType (which is really a category of membership) I want the Dues field to have the applicable default value automatically entered.
For MembershipYear > 2008
If TransType="Individual" or TransType="I" Dues=35.00
If TransType="Family" or TransType="F" Dues=50.00
If TransType="Founder" Dues=100.00
If TransType="Student" Dues=10.00
If TransType="Lifetime" Dues="" or is Null
If (TransType is Null or TransType="") and TotalPaid >0, Dues=35.00
I have created code to import and excel file and create a table from that info. Now I need to confirm that the import has the correct Datatype of Number and Fieldsize of Double for one of the columns.
View 2 Replies View RelatedHi,
I am trying to populate a recordset in
Access97 using the following code, but cannot
seem to get it to work.
The table name is "EventData" that has Type and date fields.
startdate and enddate are text boxes on a Form, for wich the user
enters the date range, and then clicks a command button to
build an Excel spreadsheet on the fly that will be populated with the
results of the recordseet.
here is the code:
Dim db As DAO.Database
Dim rst As DAO.Recordset
strSql = " SELECT EventData.EventType, Count([EventData].[EventType]) AS [Num Occurrences] FROM EventData WHERE ((EventData.Date) >= (Me!startdate)) GROUP BY EventData.EventType"
The error message that I am getting is "run time error 3061", too few paramaters. ALSO not sure if I need a semicolon right before the closing quote of strSql.
Any help is appreciated.
I am using the function below to calculate a field that excludes weekends and holidays. The weekends are excluded as it is now, but when I try to add in code to exclude holidays I am getting errors. The code for the holidays is in bold and a couple of the errors are Loop without Do so I remove the Loop then I get a Else without If.
Code:
Option Compare Database
Public Function WorkingDays(Due_Date As Date, Result_Date As Date) As Integer
'-- Return the number of WorkingDays between Due_Date and Result_Date
On Error GoTo err_workingDays
[Code] ....
I am looking to add multiple lines of record to a sub-form via a module activated from another linked sub-form.
As an example of what I want to do:
On identifying a particular type of vehicle servicing (e.g. 6000 mile service, 12000 mile service) on a service event sub-form of a vehicle form, I want to enter a list of required parts (e.g. oil filter, oil, etc) on another sub-form called "parts", that I can then edit before recording in an underlying table.
Can anyone help.
Thanks
Steve
:confused:
I want my form frmintake to lookup with values from another table and populare county once the zip codes has been entered.
I have a table called tblintake and another tblcounty.
tblintake has zip codes entered manually
tblcounty has zip codes and counties for all regions
how does this work out in dlookup.
=DLOOKUP[Zip Code],[tblCounty],[zip Code]=[County])
Within my table if Field 1 has an answer of Self (from drop down), then, I would like Fields 6-12 to auto populate; however, if Field 1 does not have an answer of Self, then leave Fields 6-12 blank.
I am not quite sure how to lay this out. I am using Access 2010.
I'm creating a database that keeps a track of questions and scores.
The questions in the database need to be dynamic and are changed frequently.
I have a scorecard table which keeps a record of scores and the applicable question at the time the record was saved. I need to do this because in 6 months time we may want to provide feedback. As the question may have changed we need to be able to refer back to what the question was.
I want the question field in my scorecard table to populate with the value in my question table.
I have tried a number of things including setting the question field in the scorecard table to a lookup based on the following query:
SELECT tblQ1.Q1
FROM tblQ1;
This works however only as a list or combo box. I don't want the user to have to select the question. I want it to auto populate, is this possible?
I need to create a New Form control for this situation:
If I enter a date into a field and the choice for another field is equal to a certain value. How can I get the date I entered to be automatically populated into another date field.
For example:
If I enter 11/10/2005 in a date field and I choose either "BN", "BA", or "BT" in a text field, I need that date of 11/10/2005 to be automatically populated in another date field on the same form.
Any help is greatly appreciated.
I am wanting to populate a field by entering a date in another field. I am trying to determine age(years, months, and weeks) of something by entering a date in another field. Is that possible in Access?
View 1 Replies View RelatedI have a form with a main form and two subforms. The first subform automatically populates it's linked field with the data in the key field of the main form, but the second subform does not auto populate it's linked field with the data from the key field of the first subform. I have created main forms and subforms before and this was not a problem.
Is there a way to cause the second subform to automatically populate it's linked field with data from the key field of the first subform?
I need a continuity field...transaction number..that can be both a parent and child key. Would like to create this upon accessing a new record / row.
View 3 Replies View RelatedHow do I populate fieldA in formB with FieldB from FormA..
So Field is the last numeric field in Form A..so I need to the take the calculation value of that field and be the starting value in formB.
automatically populate the value of a field [Industry] in a table after a user has entered the value in another field [IndustryCode] in the same table. The [IndustryCode] and "Industry" are stored in a separated table [2012IndustryCodeTable]. I have tried using DLookup and cannot get it to work.
Ex.
|IndustryCode|Industry|InvestigationDate|
|001 |Car |
|002 |Plane |
How can i get the [Industry] to populate the information from the [2012IndustryCodeTable] once a user enters the code into the [IndustryCode] field in a table?
I want to fill my 2-column listbox on a form with the following VBA code:
Option Compare Database
Private Sub Form_Load()
Dim proefsel As Integer
Dim nproducts As Integer
[Code]...
When i run the code i get a compile error on the code in bolt. I am using access 2010.
In a form, I have a comboBox with 3 columns.I update the field and with this piece of code, I update two more fields based on this comboBox data/columns.
Code:
Me.EUR.Value = Me.Combo37.Column(2)
Me.USD.Value = Me.Combo37.Column(3)
I wonder if I can update fields which are not parts of a combo.For example, suppose I have a form with two fields, DATE and USD, Once I update the DATE field, this event, auto populates the USD exchange rate field.
I have some code that successfully gathers some items and adds these items to a listbox, one item at a time using .AddItem.And it works ok.The problem is, when there are a bunch of items to add, everytime the .AddItem runs, the form redraws.there are a bunch and this leads to a lot of flicking while the form redraws however many times .AddItem adds an item.
I've had the idea of building an array and then assigning the array to the listbox.I can build the arrray no problem but i have not been able to assign the array to the listbox all at once.how to do is add items to the listbox from the array one specific item ListArray(i) at a time.
Obviously this would work, but gains me nothing in terms of the visual effect in the form becuase it is still adding to the listbox one item at a time.Is there some way to assign an array to a list box all at once, without doing it one item at a time?
okay guys, this is more of a vba question.
I have a textbox [txtRequestNumber] on a form which is anchored to a table from which it pulls the data. After the primary key, this is the main record tracer of my db.
What is the sytax, so that when the form [frmMainForm] is opened to a new record (on open) so that Access grabs the last [RequestNumber] from that table [tblTestRequest] and adds 1 to it.......so that users don't have to write it in themselves, but it auto generates.
a [n = n+1] type thing.
I love my dog. My wife is jealous.
I have a form titled 'New/Edit/Search Contractor Employee" that provides controls to fields within a table titled "Contractors". In this table, I have a Hyperlink field that will store the location of the files related to individual employees. On this form, I have a Control that (at the moment) the user can copy a Hyperlink and enter into this, to populate the field. This will then allow the user to click on this Hyperlink to open up the individual folder location to drag and drop whatever files they want associated to this individual record.
I have created a Command Button to automatically create a unique folder for this record, VBA below:
Private Sub Command168_Click()
strPath = "V:IntProdTrans&OpsTERMINALSDriver and Contractor Database 2014ContIndPW" & "" & txtCompanyID
If Len(Dir(strPath, vbDirectory)) = 0 Then
MkDir strPath
End If
strPath = "V:IntProdTrans&OpsTERMINALSDriver and Contractor Database 2014ContIndPW" & "" & txtCompanyID & "" & txtFirstName & " " & txtLastName
If Len(Dir(strPath, vbDirectory)) = 0 Then
MkDir strPath
End If
End Sub
What I'm trying to work out now is how to automatically enter this new folder location into the Control, which is then viewable and can be clicked on. I managed to do this (can't for the life of me remember how) but it entered the location as text and I wasn't able to click.
The Hyperlink control is titled 'Induction Paperwork' relates to a Hyperlink field.