Forms :: Retrieving A Number Based Upon Two Inputs

Oct 17, 2014

I am looking for an elegant way to retrieve a number from a table and display it in a form, where the number is determined by two values that will be input by the user to the form.

In particular, I want to be able to enter into a form:

- a date; and
- a class of asset

And have the relevant rate of depreciation for that asset class and that year appear in the form.

Sounds trivial and I guess it can be done in a number of ways. I have tried DLookup. It looks like it should work, but I have not been able to get the syntax such that it will accept a control value as a field name in the first argument of DLookup. (This would involve a table much like a spread sheet with a column for each year. I guess such tables are frowned upon and I have also heard negative comments about Lookups.)

I have experimented with concatenating the two fields into one such there is only one field to search upon. Seems to work. The table would only be updated annually and would only be a few thousand records.

I have experimented with having two joins between tables, but that did not seem ideal.

Perhaps a query of a query?

Perhaps a macro?

(Assuming I can get the form to work I would also like to create a report that lists assets and their depreciation.)

View Replies


ADVERTISEMENT

Forms :: Delete Records Based On Inputs

Nov 23, 2013

I have a form Delete which contains four fields i.e. Date, City, Depots and Vendor, which has combobox.

I am trying to delete a record from table "Sheet1" based on the combobox, for which i have written the following code, but getting an error at the lines highlighted in red :

Private Sub Command30_Click()
On Error GoTo Err_delete_Click
Dim stDocName As String
Dim intResponse As Integer
intResponse = MsgBox("Are you sure you want to delete this record?", vbYesNo + vbExclamation, "Cash Management Team")
Select Case intResponse
Case vbYes
CurrentDb.Execute _

[Code]...

View 9 Replies View Related

Forms :: Create A Filter Based On A Set Of Inputs Via Combo Boxes

Sep 19, 2013

I have a form and a subform with a master/child relationship set based on the primary key of each underlying table. All good there.Now, I want to use VBA to create a filter based on a set of inputs via combo boxes. But the filter must filter both the Parent and Child records.Example. "Show me only records where both only the Parent.Field1 = "string" and Child.Field = "string".I can do this in a QRY as follows:

SELECT Projects.[Project Number], Lessons.[Actions Resolved]
FROM Projects INNER JOIN Lessons ON Projects.ProjectsRecordID = Lessons.ProjectsRecordID
WHERE (((Projects.[Project Number])="AU-2102421") AND ((Lessons.[Actions Resolved])=True));

But, if I make this as a record source for the Parent Form, then the records in the Parent Form are repeated for each individual record in the Child form.

View 7 Replies View Related

Forms :: Retrieving Value Through Combo Box Based On Query

Sep 26, 2013

I have 4 tables:

table 1: two fields: t_code (primary key), town_name
table 2: two fields: Distri_code (Primary Key), Distri_name
Table 3:two fields: T_D (Primary key), t_code (foreign key), distri_code (foreign key)
Table 4:A_T (Prim key), T_D (Foreign key), area_code (foreign key)
table 5: area_code (pri key), area

I want to make a form on table 4 where both foreign keys are numbers. On the data entry form on this child table, i am trying to link towns and areas. At the front end form, i want a combo or list item showing town_name (table1) and distri_name (table 2).

Once the end user selects the required town and district from available options, i want to retrieve T_D from table 3 through query based on input from both values and save it table 4 in place of T_D along with area_code, which i can retrieve simply from table 5.

View 1 Replies View Related

Calculate Future Dates Based On Inputs

Jun 14, 2007

Hello,

Was wondering if it is possible to create a query or another method that would calculate future dates based on inputted info ?

For example a person inputs on a form a date completed (06/14/07) and then also selects a frequency of when this has to be revisited....monthly, quarterly, semi-annually.

So based on the date completed that the person inputs I'm trying to get the date if they select monthly of 7/14/07 (using above date example).

Any help would be greatly appreciated.

Thank you

View 3 Replies View Related

Finding Records Based On Text Inputs

Sep 13, 2005

Hello

I am trying to set up a user friendly front end to my database. I would like to have a form which allows the user to enter information into text boxes and then press a button which finds the relevant record(s).

I would like to use it to pin point an employee's record based on at least one piece of information, but possibly combined with up to four more by entering into these boxes. For example personnel number, surname, first name etc.

Is this possible and how do I go about setting it up?

I am aware of the filter function and queries but I can't seem to get either to operate in the way I have envisaged. They don't appear to offer the most user friendly solution, at least not in the format I have tried.


Apologies, but I am still getting to grips with access to be honest.

View 1 Replies View Related

Forms :: Auto Generate File Number Based On Number Of Records In Year

Jan 21, 2014

I have a form [IUDATA]

I have a add record button.

I have a date field [DATEIN]

I have a text field [DRPNO]

If the [DPRNO] field is empty, I would like the user to have the [DPRNO] field be automatically populated after the user enters a date.

I'd like the format of [DPRNO] to be "dpr YY-XXX"

Where:
YY is the year of the [DATEIN] field and
XXX is number of records in that year.

So for example, if it was the 4th record with a 2013 date the [DPRNO] would be dpr 13-004.

View 12 Replies View Related

Opening Certain Forms Depending On Inputs

Jul 22, 2015

Essentially I am trying to build a macro or even a VBA script which would open an initial form with various data entry options, and then depending on the options chosen it would open specific forms sequentially with some preset data values.

I can go into more detail if needed; however, the problem is at my work (lab in an industrial plant) my coworkers and I waste a lot of time inputting data as our forms just include every field (which only around 10% of fields are utilized for each entry) because certain circumstances will dictate what fields need to be filled in, so for each entry most fields are left blank. These circumstances are entirely predictable as to what fields I will need for each entry so I thought I would easily be able to figure this out on my own but I have been unable to do so.

View 4 Replies View Related

Forms :: How To Create A Form That Inputs Multiple Rows

Mar 18, 2014

I would like to make a form that can insert more than one row at one time. Something like add first column, then ask the user how many of the second row they would like, then prompt them for what is in the second row then add the information for the rest of the columns and have a separate row for each of the second column. So every row with have the same first column, but from there have a different row for how ever many desired in second column. So lets i enter for the first column, ABC, then I wanted 3 rows with ABC, then prompts me for the rest of the information for each of those rows separately.Something looking like this, oh and it would be updating an already existing table.

ABC|asdf|asdf|hgaf
ABC|hs|hasd|auio
ABC|JKL|ASE|ASDF

EDIT: I would also like to know if it is possible so it does it in ranges and dont have to do it manually like you enter the first column then enter a range for the second column and a bunch of rows are made with each value in between the range that was specified.

View 1 Replies View Related

Forms :: Log History Of Inputs - Using ComboBox To Update Memo Field

Jun 25, 2013

I am currently using the column history to log the history of inputs into a memo field.

But i now need to swap how my database runs and now require a combo box to have the same history function, as this is for a status updates and i require users to only input certain status's.

I no that I cant use the columnhistory command with a combo box.

When selecting a status from the combo box it automatically update the memo field (which will be hidden on the form) so the column history function works.

View 1 Replies View Related

Forms :: Hiding Certain Fields Based On Number Amount

Jun 13, 2013

I search around online and did come across much info on code to how to hide fields. But, the problem is I cant seem to find a way to hide a certain amount of fields based on a number amount.

example: Lets say I have a 12 text fields to show 12 months of monthly payments, and lets say a client only has a 3 payment term. How can I list 3 under installment field and have remaining 9 fields auto hide since they wont be necessary? this way I only see what applies and not additional text fields.

View 1 Replies View Related

Forms :: Way To Limit The Number Of Entries A User Can Make To A Form Based On A Value

Jan 20, 2015

My problem is the following: when I receive say 5 computers in a purchase form, I want to register the serial number of each of them in another form, bound to another table.If I receive 2 units in the purchase form, my user should only be able to input 2 records in the serial form (a continuous form), if we receive 3, then only 3 records,I cannot quite figure out how to build this second (serial number registration form), so that it refuses input after the correct number has been reached.

View 14 Replies View Related

Forms :: How To Get Subform To Grow Or Shrink Based On Number Of Records Returned

Aug 4, 2014

All; using 2010. How can I get a subform to grow or shrink based on num of records returned? Its a continuous subform. Tried changing Vertical and Horizontal Anchors as well as some VBA suggestions but havent been able to make any of them work.

View 2 Replies View Related

Forms :: Create Set Number Of Identical Records Based On User-defined Text Box

May 12, 2015

I'm trying to create a database for a construction company, as an experiment more than anything, and my problem is this:

I need to create a set of identical records in the table "buildings" based on the information entered into a text box by a user on the form for registering a new project. I want each record to be identical except for the "plot number" field, which I want to start at one and increase to the number of buildings defined in the text box.

"Projects" is one table and "Plots" is in the other.

View 1 Replies View Related

Forms :: Creating Lookup - Populate Contact Number Based On Selection From Combo Box

Mar 31, 2015

I want to create a text box within a form that automatically populates a contact number based on a selection from a combo box, also in the same form.

For example, I have a Bidders Table (tblTenders), this form includes information regarding the Tendor like the company name and a main contact within that company and a phone number for that contact.

I've created a separate table for all the contacts called tblContacts. This table holds all the contact information for each contact. I have a simple form called frmTenders that asks the user to input the Customer (which is the company who are bidding) and the Main Contact, which is a combo box to select the main contact from tblContacts. Below that combo box is a text box called 'Contact Number' - I want this box to display the contact number for the main contact automatically when a main contact is selected from the combo box.

The contact number text box isn't storing that information in any tables etc. It's just for viewing purposes when we need to make a call to that specific tender.

View 5 Replies View Related

Forms :: Adding Table New Entries With Form Retrieving Data

Nov 15, 2014

I have quite an extensive form linked to a table. When I add new columns to the table I seem to have a problem getting the form to read the data.I have just added a numerical column to the table and added a text box on the form that is bound to it. When I try to pull up the value using VBA it is blank, even though an entry is visible on the form. I have set the text box to general number and the entry shows in the actual table. When typing in the entry into VBA it capitalises where required so it must be registering the table entry. However the value it pulls remains blank.

View 2 Replies View Related

Forms :: Creating Number Of New Forms To Edit Number Of Tables

Oct 29, 2013

I am using a template database that I downloaded from the Microsoft Access template website and have been modifying it and adding new forms and tables etc. I have used the forms wizard to create a number of new forms to edit a number of the tables. The forms are "split forms".I can't seem to get many of the forms to stay the size that I set them to! They seem to have a mind of their own and often when I think that I have sized them correctly, I then open the form and they display in full screen mode or larger!

View 8 Replies View Related

Help With External Inputs!!

Nov 16, 2006

Hi,

I am trying to creat a DB that can potentially recieve input from a serial port which has a unique ID on it and updates the corresponding feild within the DB. I don't need to demonstrate full functionality (although if I have time I might give it a go!).

What I want to show is that the DB can accept a unique ID from this external source. The ID will just be to identify a user and a peice of equipment, potentially the feilds in the table would be updated with information like, time of session, duration, speed. I am assuming I may need to use VB to do some programming and create an incoming event. Just wondering what the most logical solution is. I want to bring up an interface when an input is recieved, like a user interface or pop up which says eg. "Hello Jim". Then it is possible to demonstrate that they have been detected.

Any help would be hugely appreciated!

Thanks,

Minxka

View 5 Replies View Related

Multiple Inputs For A Query

Jan 25, 2008

I have a form that has three different pull down menus. When I click a button on the form it opens a second form that is filtered by a query. In order to allow the user to leave a pull down menu blank the query has 6 different columns. Three are headed with the field name and three are headed with the form reference, under the form reference it either says Is Null or Is Not Null. I have a total of 8 options in order to cover all possible scenarios. I would like to be able to add more search parameters but my query is getting ridicules. :confused: Is there a way to build the same thing with a code?

Thanks!

View 1 Replies View Related

Getting Data From User Inputs

Jun 12, 2007

Hi everyone,

I am fairly new to Access, and need some help with what I thought would be an easy project. I have an Acceess table that contains data on settings for machines in my plant. There are columns for part #, Machine #, part type, etc. All in all there are about 20 different columns. Now what I need to do is have a form where a user can enter one or two fields, and print a report that contains all the information from all 20 columns.

For example, the user would enter Part number "AB1234" on machine "15-1" and would get the following data from the table:

Part # part code Equip. Temp height etc
AB1234 A 15-1 100 5.3 ...
AB1234 B 15-1 200 6.3 ...
AB1234 C 15-1 250 7.3 ...

They could then somehow choose exactly which record they wanted (example : ABC1234 B) and print a report that contained just the data from that selection.

I hope I explained this clearly enough. Basically I want a user to be able to enter 1 or 2 selection fields and then be able to print a report that contains info from 1 row in 1 table. Any help would be appreciated. I am somewhat new to this and havee a basic understanding of Access and A moderate understanding of VB. Thank you in advance for your help.

View 1 Replies View Related

Tables, Strucuture, Multiple Inputs

Aug 29, 2005

Howdy. I have a question on design of tables. Most of the databases I have seen developed here (and in books) assume that there is some kind of individual input of data. Thus, one major table for input (of various sources) worked well on my other projects. This one doesn't seem to fit the mold.

I have Excel/CSV files from five different vendors. Each deals with a different kind of data. That is, while all of them will have a few common elements, there are several significant differences in the other fields that I cannot bring them into the same table. For instance, in one table one field might be "Size" and refer to inches, while another table would have size, but it refers to the portion of the page (1/4, 1/3, etc.). Thus each of the five vendor tables have unique fields.

Tables:
Input tables:

tblTV
tblNPP
tblRAD
tblOOH
tblONL

Ref Tables:

tblSEGMENT
tblMEDIATYPE
tblDMA
tblDAYPART

For instance, fields for tblTV

TVID
Station
DMA
Daypart
Program
Length
DateStart
DateEnd
TRP
NumberSpots
Cost
MediaType
Cons/Bus
CreativeDescript
CreativeOffer
Segment

So, my questions:

1. Is it acceptable to have five input tables, rather than one?

2. These five vendors will update their input data on a daily weekly basis. This, there will be changes to some fields and addition of many new records. What is the best way to handle this?

3. In addition, we will have 2-5 people updating several fields (i.e. last five in tblTV above) that the vendors cannot supply. Will forms be the easiest way to accomplish these updates?

3. Relationships will be critical in getting this set up correctly. Any suggestions?

Any kind of direction, suggestions will be very much appreciated.

View 8 Replies View Related

Trying To Prevent Duplicate Data Inputs

Mar 23, 2005

I have a form that contains 5 fields and want to ensure that duplicate information is not entered. I am aware that when creating the form you can set Duplicate Records to No but each record has an AutoID which I assume means that even if the information is the same it will not register as a duplicate record due to the different AutoID.

There should only ever be 5 records entered per day. One for each region

I have created a Sanity Check that is displayed as a sub form that shows if the relevant information has been entered for a given day in Bookings and Backlog but this doesn't stop the user from putting in the same information twice. i.e. the User starts inputting the information gets called away and then trys to put it in again but doesn't check the sub form.

The Table in which the information is stored is called Bookings and Backlog.

The 5 fields on the form which is called Bookings and backlog are

ID (Autonumber)
Date (Short Date)
Combo26 (Text) (5 Regions:- North, South, East, West, Central)
Bookings (Number)
Backlog (Number)

I have put the following statement in AfterUpdate on the Combo26 field

If Me.Date = DLookup("[Bookings and backlog]![Date]", "[Bookings and backlog]", "[Bookings and backlog]![Date]=[Date]") And Me.Region = DLookup("[Bookings and backlog]![Region]", "[Bookings and backlog]", "[Bookings and backlog]![Region]=[Combo26]") Then
'Define Message Box
MsgBox "The Region you have chosen already has data entered for this day." & vbCrLf & vbCrLf & "Please check your records and amend were neccessary.", vbCritical, "USER INPUT ERROR"
Else
End If

However I'm not to hot with writing VBA and probably got something wrong somewhere as it doesn't seem to do anything and lets the user progress regardless. I have tried amending the above script and asked it to look at just one field but this just seems to give the message regardless of the information selected in the Combo box.

Can someone give me an indication as to what I have done wrong so I can put it right and in a language that is easy to understand so that if I have a similar issue later on I don't need to post another question.

View 3 Replies View Related

Mulitple Inputs To A Single Field Query

Feb 10, 2006

Hey guys, my problem is this:

I want to run a query that runs off of two list boxes on a form. However, I want both these parameters to filter a single field in my query. Why I want to do this is I have several types of accessories made by several manufacturers, both included in the "Description" field. I don't want to include an entirely new field for manufacturer because it's only 4 out of about 5 items that need this.

Is there a way this can be done or will I be forced to include a manufacturer's field.

Thanks in advance
Nathan

View 3 Replies View Related

User Inputs Table When Query Runs.

Mar 14, 2008

I have a database with 96 tables.
8 tables per month
one query pulls the desired information.

My question is this, currently the query looks to all the January Tables.

Can I configure one query to request user input(month) and then the query looks to the tables based on the users input(month).

View 4 Replies View Related

Queries :: Way To Construct A Query From Selected Inputs

Nov 27, 2014

I have a form with several fields and combo boxes on which the user will select different criteria to generate a report.The default value for each of these fields/cbo's is "*", so if the user changes nothing from the default, I execute a basic "SELECT *" query.

If however the user enters some value (selects a specific date range, customer number, salesman,etc), I would like to 'easily' modify my query to accommodate the entered search criteria.Trying to construct SQL when there are many search options like this is difficult. I took a stab at using QueryDefs but I'm struggling to find example VBA code as an example.

View 1 Replies View Related

Reports :: Report Not Filtering Correctly With Date Inputs?

Mar 13, 2013

My report generates base on 2 date inputs(from textboxes) namely, ProcStartDate & ProcEndDate & a combobox(cmbRptSupplier) to select the supplier:

Code:
Private Sub btnReport_Click()
Dim strDocName, strWHERECondition As String
strWHERECondition = ""
If Me.cmbRptSupplier = "" Or IsNull(Me.cmbRptSupplier) = True Then

[Code].....

When I leave my cmbRptSupplier blank (so that I can leave supplier out of my criteria), & fill in 1/2/2013 for ProcStartDate, 14/3/2013 for ProcEndDate, I get ALL the invoices displayed in return for my report. Even with those before 1/2/2013.

View 10 Replies View Related







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