Modules & VBA :: Making A Field Required Based On Value Of Another

Sep 6, 2014

I have a subForm set to continuous forms. I need a field called txtStatusDetails required (cannot be null) if the field txtDutyStatus = "Not Available"

Here's what I have so far:

Private Sub Form_Current()
' This procedure runs after the record changes.
On Error GoTo Err_Handler
ResetAvailability
Exit_Proc:
On Error Resume Next
Exit Sub

[Code] ....

View Replies


ADVERTISEMENT

Making A Field Required Based On Value Of Another Field

Jun 18, 2015

I am trying to do something that I would think is very simple but I'm having a hard time with it. I have a form that consists of questions that the user is needed to answer. It displays the question, a combobox where the User can select 'Yes' or 'No', and a textbox where the user can input their comments. What I would like to do it the following: if the user selects 'Yes' as an answer, I want the corresponding comments box to be required.

View 10 Replies View Related

Modules & VBA :: Formula Based On Record Number - Recordset Required?

Dec 14, 2014

In order to arrive at the required solution I have to perform a calculation, using data from each successive table record (in chronological order). The result of each calculation must then be used to arrive at the result for the next calculation.

In other words I have to update a variable based on data from each record, sorted in chronological order, and use the final result to populate a field on a form.

Is it time to break out my copy of "VBA For Dummies" and start learning how to use Recordsets?

View 8 Replies View Related

Making All Fields Required

Mar 4, 2005

I have a form that I want to make all fields required so that the user cannot leave that form until all fields are filled out. I have the Required property in the table set to yes, and this works fine if I go to the form from the database window. However, If I go to the form from the main switchboard (In Add Mode), the form can be closed without all of the fields being completed. I know it's probably something pretty simple, but I haven't been able to figure it out. Any help would be greatly appreciated. Attached is a picture of the form if it would help at all.

View 10 Replies View Related

Modules & VBA :: Adjust Required Field - Space Bar Character

Aug 30, 2013

How can I adjust my required field so that you cannot simply enter a space bar character to circumvent the requirement?

Here is part of the code:

If IsNull([txtRequiredReason]) Then
MsgBox "The Reason field is required"
DoCmd.GoToControl "txtRequiredReason"

Else

View 1 Replies View Related

Modules & VBA :: Updating A Field In A Form Based On Another Table Field

Sep 13, 2013

Using Access 2010. Fairly new to automation and macros.I have two tables (tblProductReceived and tblBins) and a form (frmProductReceived).A field in tblBins corresponds to a location the bin is at (named BinLocationID) and the tblProductReceived table tracks product that a specific bin has received.

What I need is for the tblProductReceived field PRLocationID ([tblProductReceived].[PRLocationID]) to be automatically populated with where the bin is at ([tblBins].[BinLocationID]) when selecting a specific bin in the form (frmProductReceived).

View 1 Replies View Related

Making A Selection Based On A Month

Jan 15, 2006

Hi

I have a subform on which I log the times spent on any particular project.

I have another subform on which I want to select a month and use this to select all the instances on which work was done on any given project for a particular month. I would prefer to use a pop up calender to select the month (i.e. by slecting the last day of the month in question)

Has anyone got any idea how I might do this - I am completely stumped.

Thanks in advance

Cheers

Rob

View 14 Replies View Related

Making A Calc Based On A Table

Mar 7, 2006

Hello everyone. I am very new to databases, may have bitten off more than I can chew, but I was tried of working in 4 different spreadsheets and decided to pull them all into a database so that I could get what I wanted when I wanted. Here is what I need to be able to do: In a table I have 3 different industries entered 5 different times. They all cover a Monday to a Friday. Each day for each industry I record a reading from a meter. I make a calculation by subtracting Mondays number from Tuesadays, Tuesday from Wednesday, Wednesday from Thursday and Thursday from Friday. This calculation gives me how much water each industry used each day, over 24 hours. Doing this in a spreadsheet was simple. My table has the Industry name, date, previous day meter reading, present day reading. I want to have the calc show in my query. How do I set up this calculation. Any help would be greatly appreciated. Thank you. I attached a sample database that contains the table and query.:confused:

View 3 Replies View Related

Modules & VBA :: Update Field Value Based On Another Field

May 11, 2015

I have the following fields in an A2010 form

PaymentMethod
Total
InstalmentAmounts
OurFee

InstalmentAmounts field is populated based on the results of PaymentMethod which is a combo box

Here is the current code

Select Case Me.Payment_Method.Value
Case "Three post dated Cheques"
'If three payments by cheque
Me.InstalmentAmounts.Value = Round(Me.Total.Value / 3, 2)

In some cases there will be a few pennies that the client "overpays" because the instalment amounts will not add up to the Total. I need to add up the TotalInstalmentPayments to find out what the client actually pays and then add the difference between TotalInstalmentPayments and Total to the OurFee field

So

Total = 500.00
Payment Method = 3 instalments so
IstalmentAmounts = 166.67 (rounded) so client pays 500.01 so the 0.01 needs to add to the OurFee field.

View 4 Replies View Related

Modules & VBA :: Auto Increment Field Based On TWO Other Fields

Jun 5, 2013

I'm making up a stock system for the independent jewellers I work for. Each stock pattern needs an individual code which can quickly be recognised read as tickets are often taken off the jewellery and can be mixed up easily. To this end we categorise based on two criteria:

1. Material
2. Type

At the moment these are as follows:

MATERIAL

IDMaterial
0Base and Miscellaneous
1Silver
2Gold
3Palladium
4Platinum
5Pewter

TYPE

IDStock Type
0Previously Owned
1Ring
2Pendant
3Chain
4Necklace
5Bracelet or Bangle
6Earrings
7Brooch
8Gents
9Gift, Clock or Miscellaneous

So if I had three pairs of silver earrings I want the first to be No. 161, the next 162, the next 163. If I had three Gold Bracelets I'd want them No. 251, 252, 253 and so on...Material and type are both selected form separate Comboboxes on a form.

View 2 Replies View Related

Modules & VBA :: Update Table Field Based On A Query

Nov 16, 2013

I have a table 'table1' which has various field including an ID field and a yes/no field. I then have a form based on a query originally derived from data in the original table. The form provides a list with some ID's with a yes and some with a no.

I'm trying to write a button code to convert all the no's into yes' for those ID's picked out by the query.

I've searched lots of sites and get that I need to set recordsets for both table1 and the forms' query but all my efforts crash or give an error.

I am looking for the right way to say .." for each ID in form set the yes/no field corresponding to the same ID in table1 to yes"

I'd attach my version ....

View 2 Replies View Related

Modules & VBA :: Edit Font Color Based On Field Value & Age

Mar 4, 2015

I am attempting to adjust the font color of a date field on a report based on the value of two other fields. I have the below code set in the "On Format" property of my detail section - however it does not work when I open the report to view.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Me.Frequency = "Annually" Then
If Me.ClassDate < (DateAdd("yyyy", -1, Date)) Then
Me.ClassDate.ForeColor = vbRed
Me.ClassDate.FontBold
End If
End If
End Sub

View 1 Replies View Related

Modules & VBA :: SQL Query Field Based On A String From Input Box

Sep 10, 2013

How do I use a update sql query to update a field based on a string from an input box. Heres what i have been trying:

Code:

Dim NETWORKBOX As String
NETWORKBOX = InputBox("NETWORK TO IMPORT" & Chr(10) & "EXAMPLE: PRIMARY", "NETOWRK TYPE")
sql = "UPDATE " & TABLENAME & " SET NETWORK = " & NETWORKBOX & " ;"
DoCmd.RunSQL sql

If i run the code and input 'PRIMARY' in the NETWORKBOX the sql query will return an 'enter parameter value dialog box' with the word 'PRIMARY' above the input field.

View 1 Replies View Related

Modules & VBA :: Lock Certain Fields On Form Based On Value In A Particular Field

Aug 4, 2015

I'm trying to lock certain fields on my form based on the value in a particular field. It seems to work the first time but applies itself to all other records in the database from then on!

The starting point is a command button that sends an email and makes Me.DPLLock = 1. The corresponding table entry is formatted as a number.

The code is...

Private Sub Form_Current()
If Me.DPLLock = 1 Then
Me.OR_Name.Locked = True
Me.OR_Sales_Order.Locked = True
Me.OR_WO_No.Locked = True
Me.OR_Qty.Locked = True

[Code] ....

Ive tried Me!DPLLock with the same result.

View 7 Replies View Related

Modules & VBA :: Pick Field Based On Number Of Clicks

Apr 23, 2015

I'm incrementing a counter every time I click on a combobox by using the on click event and this code:

Code:
POcount = POcount + 1

I have 5 fields on my form: GWS1, GWS2, GWS3, GWS4 and GWS5.I want to store the value of the combobox when clicked into the correct GWS field. For example: If I click the combobox 3 times it would store the value of the combobox after the 1st click in GWS1, after the 2nd click in GWS2 after the 3rd click in GWS3.I tried

Code:
me."GWS & POcount" = combobox1

but that's invalid syntax.

View 4 Replies View Related

Making Join Between Numeric Field And Text Field

Dec 23, 2006

I would like to make a join between two field in 2 different tables:

1.) Table with number field for Purchase Order number
2.) Table with text field for customers PO ref (where normally above PO number is entered)

When I link the both I get an error/warning message that the "types in the expression don't match"

Can anyone help me to overcome this problem ??

Thanks

View 3 Replies View Related

Required Field

May 8, 2006

Hi. I want to have all my fields on my form to be required and so that the user cant close the form if they are empty (error message). In the table properties I have them set to Required an have tried putting in a validation rule of IS NOT NULL but it still dont work. any help appreciated. thanks

View 1 Replies View Related

Required Field

May 8, 2006

Hi. I want to have all my fields on my form to be required and so that the user cant close the form if they are empty (error message). In the table properties I have them set to Required an have tried putting in a validation rule of IS NOT NULL but it still dont work. any help appreciated. thanks

View 1 Replies View Related

Modules & VBA :: Listbox - No Given Value For One Or More Required Parameters

Jul 14, 2014

I'm populating my listbox from access but it doesn't like my SQL statement, where am I going wrong

sSQL = "SELECT * from Process where ParentName = '[cboCategory]'"

Process is a table and cboCategory is the excel form control.

The error is "No given value for one or more required parameters.

View 3 Replies View Related

Modules & VBA :: Open Form On Required Tab

Jun 11, 2013

I have a list box that displays records. When i double click a record it opens the form needed.However I want it to open the form but default to a specific tab. But when it goes to the tab, the subform within it needs to match the ContractID of the record they double clicked on in the search form?I currently have this which opens the Client Form based on the ClientID of the listbox query search results and the client id of the main record.

Code:
Private Sub SearchResults_DblClick(Cancel As Integer)
If CurrentProject.AllForms("frm_Clients").IsLoaded Then
DoCmd.Close acForm, "frm_Clients"
End If
DoCmd.OpenForm "frm_Clients", acNormal, , "[ClientID] = " & Me.SearchResults.Column(0)

So in theory If doubleClick then open Client Form on Tab3 where subform Contract field ContractID is matches the ContractID in on the search results?

View 2 Replies View Related

Modules & VBA :: Adding Various Values Based On Select Case To Value Of Field

Oct 1, 2014

I am trying to add various values based on Select Case to the value of field. The problem I face is that each time when I get different Case in select statement, the value of the field rather changing adds the value on top.

Code:
Private Sub ProductID_AfterUpdate()
Dim qflPrice As Variant
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sqlQry As String
Dim instID As Integer

[Code] .....

View 5 Replies View Related

Modules & VBA :: Search 1 Field Based On Multiple User Input

Jul 4, 2013

I have a search form that looks up a value in a field and lets the user know if it is there or not, they have now asked to put in multiple values to search... eg. searching containers number, I will put user input box where they can put these numbers

NYKU023561
TRLU102356
TCNU123023

This will search my container number field and show a pop up message box saying..
Please note below
NYKU023561 - has been found you cannot use this container
TRLU102356 - has not been found please check internal system
TCNU123023 - has beeen found you cannot use this container

Below is the basic search I had before which was all I needed.. how can I adapt this?

Code:
Private Sub Command256_Click()
If (CntSearch & vbNullString) = vbNullString Then Exit Sub
Dim rs As DAO.Recordset
Dim CNT As String

[Code]....

View 4 Replies View Related

Modules & VBA :: Conditional Reporting Based On Value Of A Field In Selected Record

Mar 27, 2014

I have the following code that I need to modify:

Code:

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.

View 4 Replies View Related

Modules & VBA :: How To Make Particular Text Field BOLD Based On Value From Query

Jul 20, 2015

I am using MS Access 2007 and have a report which display the list of values (say 10 fields) from the basic SELECT * FROM table query.

I need to dynamically set the BOLD based on the particular value for the fifth field which was assigned from Query. if it doesn't match it should display normal.

E.g
If Field_5 = "Agent" then
Field_5.FontBold = True
Else
Field_5.FontBold = False
End if

I tried the aforementioned code and it dont work. ALso i tried achieve it using below listed options.

Me.Field_5.FontBold = True
Field_5.Properties("fontweight") = 700

But nothing worked.

View 5 Replies View Related

Modules & VBA :: Insert CR / LF In Memo Field Based On Search String

Jan 16, 2014

I import a CSV field which has not preserved the CR/LF when it was exported from BCM Remedy. There is no setting for that. The memo field prints on my report like this:

A custom solution would be developed, that once implemented, could become the standard product in similar situation. Normally would assign to Network Engineering, but will work with Ray and the IPT Team to cost out the solution and get approval to proceed. 2012/05/24 10:44:28 AM PCOLLINS Sent to Ray Massie for review to determine if a solution needs to be proposed, or if they can wait for the National IPT solution to be ready in 2013.

I want to add VBA code that inserts a CR/LF in the memo field before all but the first occurance of a string that looks like a date, the first occurance doesn't need it. I will do it right after I import the CSV file into the table, so it happens only once, and it always prints and displays the CR/LF.

The memo field is called "NBS Update" and the table is called "CCRR Remedy Data"Here is what I have, but don't actually know what to put in to find the date and add a CR/LF:

Code:
Dim db As DAO.Database

Code:
Dim db As DAO.Recordset
Dim srtSQL As String

[code]...

View 14 Replies View Related

Modules & VBA :: Display / Hide Subforms Based On Yes / No Field In The Form

Oct 4, 2013

I have created a form with 3 subforms on. i was just wondering is it possible to display/ hide these subforms based on a Yes/No field in the form. as the subforms would only be valid if the field is ticked as yes.

View 14 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved