Tech Problem Aggregator

Solved: Access 2003 - Hiding Parts of Forms/Fields

Q: Solved: Access 2003 - Hiding Parts of Forms/Fields

Hey everybody

Okay, been playing around with our Access Database here.

I was wondering if there is a way to hide certain elements on a form, when a button is clicked to view the form?

For example, I have a form called newHireForm, that is shown when you click on a button on the switchboard. Now, I also have a form called recruitingContactForm, which has a lot of the same information that newHireForm has, except it has different dates, and a few different fields.

Now, I figured I could just have one central form called newAgentsForm, for instance. Then on my switchboard have a button for seeing New Hire, and then a button on the switchboard that lets me see Recruits Contact. Is there a way to let certain fields be visible, while others are not based on what button I press on the switchboard?

I hope I was pretty clear with explaining. It's hard to put in words exactly what I want to do.

Thanks

A: Solved: Access 2003 - Hiding Parts of Forms/Fields

8 more replies
Answer Match 92.82%

My Form access a Query to retrieve 3 fields from 2 tables :

SELECT tblA.X, tblB.Y, tblB.Z
FROM tblA LEFT JOIN tblB ON tblA.X = tblB.X

There are lots of rows being retrieved.

My Form has X set up in a combi box.
I wish to select the downarrow for X to select any row,
and then for Y and Z to redisplay the corresponding values.

Eg : if the 1st row displayed is X1 Y1 and Z1, and I scroll down to row 20,
I want to see X20 Y20 and Z20.
But at the moment, I get X20 Y1 and Z1.

I suspect I will need to build an On Change Event Procedure for X,
to force Y & Z to be displayed, but I do not know how.
Many thanks in advance
 

A:Solved: Access Forms: redisplay fields after selection from a dropdown

6 more replies
Answer Match 85.26%

Hi there,

Let my preface this post by saying that I have some experience working with Access, but nothing official and so what I know is as a result of hacking my way through. Also, let me start by saying that I apologise if this seems long winded, but I am unsure how much info to give.

I've inherited a database at work which I need to develop further, but it uses some methods I am unfamiliar with, though I think I understand enough to know they need to be there.

Basically the database is a customer transaction system and its that particular section I am trying to sort.

I have a table (tblPupilTransaction) which is meant to be updated whenever a transaction occurs. It has :

Transaction Date
PupilID
EAN (item code)
Billing Status
Quantity
ValueExcVAT
ValueIncVAT

However, because there will only be a static group of customers that can purchase from us, we have a lookup table (tlkpPupil) which has :

PupilID
FamilyName
GivenName
CurrentFlag

My difficulty comes when developing the form that would be used to input the transactions. I had thought to use combo boxes to link FamilyName, GivenName and PupilID into one selection, but that doesn't automatically complete the exisiting PupilID field (which cannot have a null).

Likewise, when I use a combo box to put in the product data, the price is not automatically complete (these also have the appropriate lookup tables).

So, in short, I want to know how I create a form which will input into tblPupilTransaction w... Read more

A:Bound Fields on Access Forms

You are on the right track. Have the data in the Combo boxes. All it takes then is some simple VBA code in the Combos After Update Event Procedure to pass the values to the fields.
However, the only thing that should be stored for the Pupil and Product is the ID numbers.
The rest should only be "Displayed".
 

3 more replies
Answer Match 83.58%

I need to require that certain fields in a form be populated before a user can move on to the next record in that form.

I included the following formula in the BeforeUpdate property for the form:
=IIf(IsNull([Combo40]),MsgBox("Please select a value for SAP Adjustment required"))

I've tested this formula and it works. The problem I have is when I click OK on the message box the form advances to the next record. I would like to keep the same record visible that prompted the message.

Can anyone advise how I do that? Is there something wrong with my formula or do I need to do something else?

Thanks!
 

A:Solved: Access 2003 Forms

How are you inserting the data? With a button click, or moving off the last field? If your using a button, I would use the Click event. Test each field's text box/combo box/etc to see if they contain data AND the proper type of data (IE you expect Alpha characters in a test box and someone tries to enter numbers). Once all your conditions are met, then and only then do you add the data to your table. Otherwise, move the focus back to the control that needs data.

IF your inserting data by moving off the last field, try using the before insert event Do your checking here. You might also check out the On Dirty event (either use the on-line help or do a google search).
 

1 more replies
Answer Match 82.74%

Hi, I have one table (Geocodes) who has identical fields as another table (PROJDES1) except that its fields have updated contents and I would like to merge the fields contents into the other table (PROJDES1) without touching the other fields in that table. I am attempting to do this with the following code:
Code:
DoCmd.RunSQL "UPDATE Geocodes INNER JOIN PROJDES1 " & _
"ON 'Geocodes.Project #' = 'PROJDES1.Project #' " & _
"Set Geocodes.LAT = PROJDES1.LAT " & _
"WHERE Geocodes.LAT <> PROJDES1.LAT " & _
"OR PROJDES1.LAT IS NULL " & _
"OR Geocodes.LAT IS NULL"
But when I run the function access says "Join expression not supported.", what am I doing wrong? Thanks!
 

A:Solved: Access 2003 merging fields from two tables

8 more replies
Answer Match 82.74%

I use Access 2003 to produce school reports. Marks come under columns Term1, Term2 and Term3. I want to calculate the average of these term marks in column 4 just after Term3 mark. Term2 and 3 might not have any marks(when we are still in Term1 obviously) but I want the formula to count the number of fields with marks/values (Do not count empty fields) and hence calculate the average.
Using the Count function has not helped as it counts data in columns.
 

A:Solved: Calculate averages of fields in Access 2003

6 more replies
Answer Match 82.74%

I am experienced with computers but somewhat new to access. I want to create multiple tables for my customers but have the customers ID carry to each table. For example, if a customer enters their information they are assigned a customerID automatically in one table. How do I get that number to copy over to other tables?

TblCustomer.CustomerID = 1

automatically copy that to

TblPurchase.CustomerID

and

TblXXX.CustomerID.

Thank you in advance to anyone who helps.
 

A:Solved: Access 2003 help... Copying fields between tables

gatormrr, welcome to the Forum.
The normal way is to set up the correct One To Many Relationship and then use a Subform to enter the "Sub Table's" data. When the Mainform (Customer) and the Subform (TblPurchase) have their "Master/Child" Links set to CustomerId the Subform automatically enters the CustomerID when a record is created.
The Mast Table CustomeriD should be an Autonumber field but the TblPurchase CustomeriD field Should be a Number type Long.
If you do an Advanced search for "User Name" OBP you will find lot's of examples that I have posted showing how this is done.
 

2 more replies
Answer Match 81.9%

Hi all,

I am trying to make a query in Access to sum different fields (columns) based on a personal number. So, my table consists of:

personal number, yearborn, title, loan, loan2, loan3, %, %2, %3. I would like to add the loans together based on the personal number and the percentages together based on the personal number. I can't figure out how to do this with the sum because the sum only takes 1 parameter like loan. Can somebody help me out??

Thank you.
 

A:Solved: Access 2003 SQL suming different fields depending on person

16 more replies
Answer Match 81.9%

I have a simple query that pulls information from a table. The query works and shows the correct 1 to 20 items. I have used the form wizard to create a form off this query, but the form shows blank spaces instead of the data shown in the query. the number of blank fields matches the number of results in the query.

I'm using Access 2003 on Windows 7.

any help will be appreciated
 

A:Solved: Access 2003 form showing empty fields

7 more replies
Answer Match 81.9%

Hello All!

I searched around on the forum and online but didn't see any questions like mine (or that I quite understood...Remember I'm a beginner). So maybe you guys can help... My overall objective is to create a crosstab query that will show the total cost per month.

Here is what I have:

I have 4 fields (all in the same table):
Shipping Cost to Return
Shipping Cost to Send
Product Cost
Total Cost

I have a form:
That I manually enter 3 cost values in and it generates the total cost by using the formula:
=Nz([Shipping Cost to Return],0)+Nz([Shipping Cost to Send],0)+Nz([Product Cost],0)

--this formula is in the "control source" box under properties.

The Total Cost that is generated for each record doesn't show up back on my table. What's the deal and how can I get it to show up?

Thank you,
-Julie
 

A:Solved: Access 2003: how to create field that shows sum of other fields.

16 more replies
Answer Match 81.9%

Hello

Using continuous forms, I will see a series of my records, stacked one on top of the next. Is it possible to devise a way to highlight areas on ANY record related to values in the record.

Example.

Two records (R1, R2), if a date field in R1 is less than 30 days away from Now(), then show a flag (maybe image.visible=yes) only for that record.

Meantime, R2 does not need to show the flag because the rule applied does not evalute as true.

I don't think this is do-able - you guys may know otherwise?

Thanks

Chris
 

A:Solved: Access 2003 - Use of Color in Continuous Forms

6 more replies
Answer Match 81.06%

I have a table of people data with multiple fields for the name: Prefix (Mr., Mrs., etc.), FirstName, MiddleName, LastName and Suffix (Sr., Jr., etc.). All fields are required except MiddleName and Suffix which often don't exist. I want to make this set of fields a multi-field unique index to prevent duplicate entries, and I have created the index with properties as follows: Primary=No (I already have an autonumber primary index), Unique=Yes, and Inglore Nulls=No. But the Suffix field is giving me trouble when it is blank as it often is. If a value is entered in this field (as with any other field) I am properly prevented from creating another record with the same values in the corresponding name fields, including this Suffix field. But if the Suffix field is blank/null (most are, because only a few people use a suffix on their names) I need that null value to be treated as a value for the purposes of determining uniqueness. Mr. Joe S. Blow should be the same as Mr. Joe S. Blow when both are missing a Suffix value, and I want the database to treat those names as the same, and prevent input of the second of them because one has already been entered.

As I understand it from Access Help, setting the index property Ignore Nulls=No should accomplish this. I understood this setting to mean that uniqueness is then determined on the remaining index fields, as though Null were being treated as a value for the purposes of testing uniqueness. That isn't the case. It lets me e... Read more

A:Solved: Null fields in multiple-field indexes in Access 2003

16 more replies
Answer Match 81.06%

I was losing track of things on my website, so I thought a data base would be very helpful. I've set out to learn Access, but I'm still a beginner and I'm stuck on 3 things so far.

I have set up and related several tables including:
website pages (title, description, date written, etc.) auto number primary key
graphics (file name, size, description) file name primary key
keywords (word, stat1, stat2, stat3) word primary key

I've also set up junction tables since the website pages table has a many to many relationship with both graphics and keywords.

Question 1: How do I make a form that has multiple sub-forms?

I tried to use the wizard to make a data entry form. I wanted a form with multiple sub-forms.
If I enter the fields from 2 tables, I can easily make a form with a sub-form. If I enter fields from all 3 tables, the wizard acts completely differently and I can't figure out how to do sub-forms.

Question 2: How can I make a combo box that will cover multiple fields at once?

I fill the graphics table as I make the graphics. I wanted to use a combo box so I could choose an existing graphic OR add a new one. The combo box works fine for the file name.
The problem:
I want it to auto fill the size and description if I choose an existing file name OR allow me to enter size & description if I enter a new file name.

Question 3: How can I handle many to many relationships between records within one table?

I want to track links from each page to ... Read more

A:Solved: Access 2003 Data Entry Forms - beginner needs help please

16 more replies
Answer Match 77.7%

Hi,

I got a form A and a subform B. I use B to display data retrieved from a table while use B to simutaneously update that data. More particularly, if I click a row in B, that row will be display on A and the user will be able to edit that data. The forms were designed by someone in MS Access 97. I converted it to MS Access 2003 format. Therefore, I have to link form A and form B. However, I couldn't find Link Master Fields and Link Child Fields in MS Access 2003!

Any ideas? Thanks in advance!
 

A:Link Child Fields and Link Master Fields in MS Access 2003

climbingrose said:

Hi,

I got a form A and a subform B. I use B to display data retrieved from a table while use B to simutaneously update that data. More particularly, if I click a row in B, that row will be display on A and the user will be able to edit that data. The forms were designed by someone in MS Access 97. I converted it to MS Access 2003 format. Therefore, I have to link form A and form B. However, I couldn't find Link Master Fields and Link Child Fields in MS Access 2003!

Any ideas? Thanks in advance!Click to expand...

It really sounds like form A and subform B are both using the same table, and are not a Master/Child field link situation. But rather, form B (the subform) will have code in the "On Current" event that will find the current record on the form B within the form A, therefore allowing that record to be edited. Review your original post, I think you used the "B" one place where you meant to say "A". No matter what, you need to clear up with us out here if both form A and form B are using the same table.

Vic
 

1 more replies
Answer Match 73.92%

Hi all,

I am making a report in Access 2003. I have salaries that will be displayed by the month. However, since we are only in April, the months May-December are not in the query that the report is based on. Is there a way that I can make the report display a 0 in the place of the months that aren't yet there? The months that are there will display the salaries.

I appreciate any help! Thank you!
 

A:Access 2003 non-existing fields

Are you using a Crosstab query for the Report?
If so you can force the Headings to be entered by typing them in the Haedings Property of the Query.
 

3 more replies
Answer Match 73.92%

Hi

I have developed a simple database and used the built-in 'access search' facility that is applicable to all forms. There are around 7 fields in each table.

Problem: When I select more than one field (through the dropdown list), it lists all data instead of listing only data related to the first selected field.

Question: How do I link the fields so that during a 'search', if I click the first field and select a value (e.g. field size) then if I select the next field (e.g. colour) - it will only list colours pertaining to the selected field size - not all the colours of all the field sizes in the database table?

Any assistance appreciated.

Many thanks

Usman
UK
 

A:Access 2003 - linking fields

Hi

I requested the moderators to move this thread to the Busines Applications section. You will get more attention there, and most probably, the quickest solution as well.
 

2 more replies
Answer Match 73.92%

I am VERY new to Access 2003, and have spent $$$ on various books. I wanted to memic the Employees Form in Northwind database espically how to display pictures on the form. I copied the Employee's table and form to my new database. I also copied all of the code that went alone with the form and table. All seem to work fine until I selected a Jpeg from my folder. When I attempt to open the Jpeg, I get the following error, "Run-time Error: '2135' This property is Read only and can't be set, MS Visual Basic." It works inside of the Northwind database, but not in my database and I have no idea why it will not work. I have enclosed a copy of the VB Code and highlited where the errror is happening. I hope that someone with a lot more smarts than me can point me in the right direction.

R,
QSligh

Code:
Option Compare Database
Option Explicit
Dim path As String

Private Sub AddPicture_Click()
' Use the Office File Open dialog to get a file name to use
' as an employee picture.
getFileName
End Sub

Private Sub Form_RecordExit(Cancel As Integer)
' Hide the errormsg label to reduce flashing when navigating
' between records.
errormsg.Visible = False
End Sub

Private Sub RemovePicture_Click()
' Clear the file name for the employee record and display the
' errormsg label.
Me![ImagePath] = ""
hideImageFrame
errormsg.Visible = True
End Sub

Private Sub Form_AfterUpdate()
' Requery the ReportsTo combo box after a rec... Read more

A:Access 2003 Forms

16 more replies
Answer Match 73.92%

I have a form bound to one query. The form is distinct on name. There are several records for each person. When I update the form it's only updating the first record for that person. My fields are... name, ssn, dob, ph id and cost. The first 3 fields are the same for that specific person and the ph id is getting updated in the form. Cost is the only field that's different which creates many records for one person. I need the form to update all records when I enter the ph id. I've tried everything except VB coding. I think I may need to code a work around but don't know VB.

Thanks

A:Please Help... Access 2003 forms

Try going to the Microsoft Office section of TSF, they might be more helpful.

Edit: I see you've posted this in the Office forum already. I realize you have a problem, but please keep the post in the appropriate section and be patient, it usually takes some time for someone to post a solution.

1 more replies
Answer Match 73.92%

Hello!

I'm a major beginner trying to create an access database for client management/data reporting.

I have 6 tables and all the underlying relationships set up how I think they should go.

Here's my current hang-up (and there have been/will be many!!):

I'm trying to create an main "Intake" form that is linked to 3 tables; tblclient, tblintake, tblecase. (client has a 1-M relationship with intake, intake 1-M with case).

On the form that I created, case is a subform, while intake is integrated into the main form. The issue is a staff member will have to either fill in (or tab through) every single entry that's linked to tblclient, before they can start entering data that's linked to tblintake, and the same thing for tblcase. It seems so simple, but I can't figure it out! Is there a way around this?
 

A:Access 2003 Forms

co11co11, welcome to the Forum and the wonderful world of Access.
I am not sure why you have the intake integrated into the main form, I would have thought that the logical form design would be a Client mainform with an Intake Sunform which has it's own Case subform.

As to your question "a staff member will have to either fill in (or tab through) every single entry that's linked to tblclient, before they can start entering data that's linked to tblintake, and the same thing for tblcase", isn't that what you want them to do. ie fill in all the data?
If you just want partial filling in of data the user can use the mouse to move to the other Forms to enter data in to them.
However something needs to be in the mainform to set up the correct link to the subform(s)
 

2 more replies
Answer Match 73.08%

Hi folks. I work in the IT field but am not too good with MS ACCESS 2003. I have been given a task by my boss to get familiar with MS ACCESS. I was wondering if anyone here knows how to populate data from another table in MS ACCESS 2003? I'll give you a description. The client has a form called Referal form, Primary Referal Source and Secondary Referal source fields that need to have data populated from a referal persons table. Can anyone tell me how I can go about doing this.Thanks in advance.
 

More replies
Answer Match 73.08%

We have an Equipment Inventory database and we would like to change the sorting priority in two fields-example:
Equipment - ascending first priority - current
Location - ascending second priority - add
Description - ascending third priority - add

The report is already created and when I have went out to add the changes it doesn't change the order on the report. Not sure what I am doing wrong.

Any suggestions?
 

A:Access 2003 sorting multi fields

11 more replies
Answer Match 72.66%

Hi;

I'm trying to hide my main menu and I want only the customs menu will display at the toolbar. I made this succesfully on an unbounding blank dummy form (frmTryMenu) as the picture below.
This is the code that I used:

Option Compare Database
Option Explicit

Function showFrm1()
DoCmd.OpenForm "frmSelectTeam2"
showFrm1 = True
End Function

Function showFrm2()
DoCmd.OpenForm "frmSelectTeam"
showFrm2 = True
End Function

Function showRpt1()
DoCmd.OpenReport "rptAllEventList", acViewPreview
showRpt1 = True
End Function

Function showRpt2()
DoCmd.OpenReport "rptClass", acViewPreview
showRpt2 = True
End Function

Function showRpt3()
DoCmd.OpenReport "rptCategory", acViewPreview
showRpt3 = True
End Function

Function showRpt4()
DoCmd.OpenReport "rptEndLine", acViewPreview
showRpt4 = True
End Function

Function showRpt5()
DoCmd.OpenReport "rptLongJump", acViewPreview
showRpt5 = True
End Function

Function showRpt6()
DoCmd.OpenReport "rptHighJump", acViewPreview
showRpt6 = True
End Function

Function showRpt7()
DoCmd.OpenReport "rptShotPut", acViewPreview
showRpt7 = True
End Function

Function showRpt8()
DoCmd.OpenReport "rptStartingOfficialField", acViewPreview
showRpt8 = True
End Function

Function showRpt9()
DoCmd.OpenReport "rptStartingOfficialSolo", acViewPreview
showRpt9 = True
End Function

Function showRpt10()
DoCmd.OpenReport "rptStartingOfficialRelay... Read more

A:Hiding Main Menu... Access 2003

9 more replies
Answer Match 72.24%

I am experiencing what appears to be a really weird Access 2003 bug. I have a table with maybe 30 fields, but whenever I modify the table in design view the modified field usually disappears from design view after editing. Form controls, macros, queries, etc all still work although they may occasionally complain about the field "not existing" but then make the change anyways. Additionally, when one field disappears other ones that had previously disappeared usually randomly reappear and several random fields "s_GUID" and "TempField*0" appear out of nowhere. Compacting and repairing the database has not changed this behavior. Am I just going crazy or is this a bug inherent with the use of Access 2003?
 

A:Access 2003 table fields disappearing randomly

Try a blank database and import everything in to it.
Of course you could stop messing with it.
 

2 more replies
Answer Match 72.24%

Hey there again, I've come across another issue with my program:
I have a search form that allows the user to select a piece of equipment. A macro passes a value (EquipmentID) to a new form, and all of the fields are suppose to draw info from the table based on EquipmentID.
My problem is getting the fields to automatically update once you change the value. I started with using the ResourceSource of the forum, but users can cycle through enteries and once the Equipment ID changes the form doesn't update. So I'm down to several options:

Somehow use the ResourceSource correctly
Use a macro/code to fill the info in
Fill in a invisible field and pull the info from there

If you've got a suggestion on how this should be done, or you could let me know more on what's actually happening in Access, please let me know.
 

A:Access 2003: Autofilling fields from a Query based on a Value

The form, if it remains open does not get the new Value unless you "Requery".
To test if this is the problem you should be able to use Main Menu>Records>Refresh,
if that updates the form with your new value then that is the problem.
To me it would make more senese to have the New forma a Subform to the first form, even if it is as a Tabbed Subform.
 

1 more replies
Answer Match 72.24%

Hi all,

Ive tried a few things to solve this but cant get. Hope I can get some help here please! Here goes:

I have a query by form that allows me to search for a date range, a physician, an injury code, and three possible procedures. I have set up each procedure control (procedure_1 through _3) to retrieve data from their repspective fields in the injury table. Heres where the problem lies. I want to set up a control that looks up ALL three fields at once and gives me the result whether the procedure is listed under procedure_1, _2, or _3 field in my table. As it stands now, I have to do three separate queries with the same procedure in the different controls than combine those results to make one table and that is much too much work.

I would like to get rid of having 3 controls (one for each proc_1, _2, _3) and just have ONE control named PROCEDURE... and it will look at all of them and give results that have that procedure in any one of those procedure1,2,3 fields.

Hope this makes sense here. I am not a VBA expert and really dont have time to learn.. So I have been trying to accomplish this using the CRITERIA box in the query design. I am using the criteria:

Like [Forms]![FormName]![ControlName] & "*" Or [Forms]![FormName]![ControlName] Is Null

My control names are proc1, proc2, proc3 and I have tried combining three of these statements in every possible combination in order to achieve my results but have been unsuccessful.

Is there an easy way to do ... Read more

A:Access 2003: query by form - One control for up to three fields

9 more replies
Answer Match 72.24%

I am trying to make a database I have built available for people in my office. They will be adding data to it with the forms I have created, but I have been having a hard time finding how to set up what I have on a network so that it can be accessed by many clients.

I would really appreciate any tips as to where I can find this information. Thanks in advance!

Dane
 

A:Access 2003: Making forms available for clients

10 more replies
Answer Match 71.4%

Hi everyone.

Just wondering if someone can give me a hand. I simple used the wizard to generate a tabular form that gets the data out of a query in Access. I simply would like to click anywhere in the form and then select that record I clicked on.

Once selected the record, how can I get that record's ID so then I can run a SQL statement to delete it.

Any help would be appreciated.

Thanks.
 

A:Access 2003 Tabular Forms - Record selection

negvillamizar, welcome to the forum.
I normally get quicker responses if you post your Access questions on the Business Applications Forum.

As to your question, I am not quite sure why you need the Record's ID to run an SQL statement, having clicked on it, you can just press the delete key and it will delete the record for you.
 

1 more replies
Answer Match 69.3%

I’m working in Access 2003 building a database in which the records will all have the same basic fields, but will be a library of info covering a very wide range of topics. In order to make easier how the information is pulled out by the end user, I want to very strictly control how the info is categorized when it is initially stored in the db. I cannot allow careless input clerks to misspell any of the labels that categorize the record they are entering, or perhaps invent their own labels.
So, each record starts with several columns of categorization labels. The clerk will be presented with a series of drop-down lists and will select one option from each list in order to categorize the record. Each choice will fill in the corresponding column in the categorization section of the record. The options available in each of these drop-down lists are carefully controlled to represent the only sets of acceptable parameters to describe the information contained in the record at each level of detail. Each successive column in this part of the record, representing one of the drop-down lists, covers the next-deeper level in a hierarch of information labeling for the records. As you follow the columns left to right, you are describing the information in the record with greater and greater detail.

A simple example: The first drop-down shows the options “animal, vegetable, mineral”. You select “animal”. Upon you making that choice, the system returns to you a list of ONLY ANIMALS t... Read more

A:Access 2003 Data Input Forms for Mutually-exclusive and Hierarchically-categorized Re

blothrop, welcome to the Forum.
It is quite common. The key to successfully doing this is twofold. The first is to base each successive Combo on the selection made in the previous one, the second is to requery each combo once the previous combo has made it's selection.
If you Advance search this forum for poster OBP and Combo in the text you should find various databases that I have posted that do just that.
 

1 more replies
Answer Match 68.04%

I have an MS Access FORM which presents 2 fields from a table

X is data type YES/NO, and Y is data type TEXT.

I would like my Form to always display X, but only to display Y if X is YES.

I know I can right click on Y, then Properties > Format > Visible : No, but this isn’t sufficient.
I can also right click on X, then Properties > All > After Update which takes me into V B.

Can anyone help this layman who doesn’t know anything about V B ?

Many thanks in advance....
 

A:Conditionally hiding fields in a form

In the Form's On current event procedure and the X Field's After Update event procedure enter

if me.X = -1 then
me.Y.visible = true
else
me.Y.visible = false
end if
 

3 more replies
Answer Match 67.62%

I've created a form in Word that users fillout and print and save. Is there a way to make certain fields required?
I need it to be compatable with Word 2007 and 2002
 

More replies
Answer Match 66.36%

Anybody ever encountered this situation or have any ideas?

One of our users has a multi-tab Excel (vers. 2002, SP3) workbook with several columns containing pricing and taxes on products we handle. When she prepares to print the worksheet, she hides some of the middle columns so that only the data in the first and last columns will print out. The issue is, as soon as she unhides the hidden columns, certain data fields get wiped out... deleted. Then, she has to retype them. There are no macros or VBA scripts programmed in this workbook, and no wierd formulas I've seen that would potentially do this. Is this possibly an Excel bug???
 

A:Hiding Columns Appear to delete data in hidden fields (?????)

Sounds like a user bug. I won't believe it's exactly as you describe without seeing the file.

 

2 more replies
Answer Match 66.36%

Hi all,

I?m really in need of some help from some Access experts! I?m a newbie to Access, but I got myself busy with quite a big project... I?m trying to map out the product supply to shops in the whole country. I?ve come quite far for a starter, but I keep having 3 questions, it would be great if you could help me with this!

I have, amongst other tables, two tables called Stock_counted and Reported_quantity.

The table Stock_counted has the following fields:
- Shop_number
- Product_code
- Quantity_counted

The table Reported_quantity has the following fields:
- Shop_number
- Product_code
- Quantity_in
- Quantity_lost_or_returned
- Quantity_out

1. What I would like to do is to make it one table based on Shop_number AND Product_code. So in one record I would like to have Shop_number, Product_code, Quantity_in, Quantity_lost_or_returned, Quantity_out and Quantity_counted. How do I do this, making sure that the quantities end up behind the right product and shop? It is possible that some shops don?t have all items, while other shops might not have all products reported, or both. On top of that, I have another table with a price for each product, which needs to be related to it as well.

2. It happens that some products are found more than once in one specific shop and therefore appear more than once in the table, with the same shop number and product code. How do I automatically SUM these quantities, so that this product only shows up once per shop?

3.... Read more

A:How to combine fields in Access 2010 based on 2 equal fields?

  
Quote: Originally Posted by Josephine87



2. It happens that some products are found more than once in one specific shop and therefore appear more than once in the table, with the same shop number and product code. How do I automatically SUM these quantities, so that this product only shows up once per shop?



select shop, product, sum(quantityfield1), sum(quantityfield2)
from sometable
group by shop, product

1 more replies
Answer Match 65.52%

Hi,

I'm working with form fields in Word 2003 and I was wondering if it were possible to create a form field with a line extending to the maximum length of characters allowed? For example:

Name:_________________________________

Where the underline is the form field. I know that MS shows them using tables to create a kind of pseudo line but I was hoping for something more...elegant? One that would be incorporated right with the form field.

Thanks for all your help!

JFresh
 

A:Solved: WORD 2003: FORM FIELDS question

6 more replies
Answer Match 64.68%

I'm trying to get excel 2003 enter a row of data into a website form so i can submit it and repeat the the process with the next row...

I'm submitting inventory to a website but have the data in an excel spreadsheet, so I'm hoping to avoid manually entering each and every item onto each and every website. Some sites have a bulk upload feature using XML, but this is for the sites that don't...

Is this possible? By using excel's web query feature i can pull data from a site, i guess I'm trying to post it to the form...

Any takers?

Thank you in advance for your time and consideration...
 

A:Solved: Excel 2003 to autofill website forms?

16 more replies
Answer Match 62.58%

I have a database, and I already have several fields "locked" so that chances cannot be made to these particular fields.

I have some command buttons that I currently have enabled, but would like to disable them when a certain checkbox has a "true" value (is checked).

How can this be done (if at all) on a "per button" basis, as I have some other buttons that I want to leave enabled all the time.

Thanks in advance!
 

A:Solved: Locking fields in Access

9 more replies
Answer Match 62.58%

I have a fully functioning dbase with the last Calculation that I cannot work out.

I am attaching a sample Dbase to help w the explanation.

I would like the Bonus Total to reflect all Totals. After totaling rate and standby total, I then want to choose my services and total out the Services total and refresh the Bonus Total with all of the updated info. I have stared merciless at this and tried several ways to make this happen.

I am slowly going mad. This is the last calculation then I am finished with this but have gone blank.

If anyone can help me, I would appreciate it.

As you tab through the dbase you will see that the Base rate is divided by 2 to get the standby rate and this reflects automatically in the Bonus Total in the top half of the dbase. Then you progress to service selections and the total is reflected upon pressing enter in the Services Total. This Total is what I want added to the existing Bonus Total that is calculated in the top of the dbase.

Sorry if I am repeating myself.

Anyone?
 

A:Solved: Access, Calculated Fields, OBP?? Anyone?

Bonus total has the calculation =[cboRateTotal]+[cboStbyRateTotal]+[cboServicesTotal] as it's control source.
Once you do that you can no longer change the value using VBA.
So the answer is to replace the =[cboRateTotal]+[cboStbyRateTotal]+[cboServicesTotal] with the VBA version.
So either set the control source to nothing or make it a field in the table.
 

2 more replies
Answer Match 62.58%

I have to concatenate the following by item number: I have attached a file with a sample table.

item number / memo subject / memo text
15056 / x/ e
15056 / t / r
15056 / s / q

there is also the issue where I have ~;~ in the memo text field. This is coming from some type of code in our ERP software. I would like to have this removed and not added in the final result which would be something like this:

15056/ x t s / e r q
I do not need comma's in between the data but if the field is blank i don't need that either.

I have researched and I have tried to do some VBA with Modules but I really don't know what I am doing.... Help!
 

A:Solved: Access - Concatenate fields

16 more replies
Answer Match 62.58%

Please help. I am trying to calculate a percentage of my yes's. I have several yes/no fields in which I'll need to calculate a rate. I would prefer to create a report that will calculate this for me. However, right now, I am really just looking for the easiest/quickest way to do this. So if I need to run a query first and base the report off the query, I'll do it. Please help ASAP if you can.

Thanks,
Tonya
 

A:Solved: Calculating Yes/No Fields in Access

16 more replies
Answer Match 62.58%

I have an Access database where the data is being input in Access and displayed by Lotus Notes. Originally I had a number of Yes/No fields e.g. "Outside Storage" in the table. The person doing the Notes interface requested that I change these to Text fields which I did. The table values remained as Yes or No when I did this. The input forms kept their check boxes when I did this but as one enters data via the checkboxes the values are 0 and -1. If I delete the field from the form and add it again it comes as a text box which then displays Yes or -1, etc depending what is in the table.
The Notes person says data tables should not contain Yes/No fields or Combo Boxes like solid/liquid. My question is what is the best way to handle this assuming the Notes developer wants text fields in the table? Should I delete all the checkboxes from the forms and replace with text boxes showing Yes or No? Right now I have the validation of the table set to accept yes, no, 0 or -1. Finally, is the field on the form able to be changed back to a checkbox without changing the table field back to a Yes/No field? It seems as if the type of field placed on the form is inherited from the table.
 

A:Solved: MS Access Yes/No or Text Fields?

7 more replies
Answer Match 62.16%

Hi

I am new to access and am designing a database for staff that have varies degrees of computing knowledge.

I have the structure of the database in place and am now creating form for the users to input information. I want the new to be blank everytime a user opens it, though at the moment the forms display the first record of the corresponding table.

I really need to default of the forms to be blank as I don't want staff over writing the data.

You help is appreciated thanks.
 

A:Solved: Ms Access Forms

You will want to go into the form's property sheet. Try double-clicking on the "Form Selector" which is the little box in the top left-hand corner of the screen. In the "Data" tab within the property sheet set "Data Entry" to use by double-clicking on the "No."

Have you looked into making a switchboard which is an interface that allows them have all of the forms, reports, queries etc that you want them to see?
 

2 more replies
Answer Match 62.16%

Hi all

This is my first post on here, so please be gentle!

I am a relative newcomer to Access 97 (which we still use at work), and have created a form which includes customer records. However, what I have now been asked to do is beyond me. One of the fields can be either empty or populated. I have been asked to ensure that if the field is empty it cannot be amended, but if it is populated, it can be changed if necessary (but not to an empty field). I currently have it set up as an enabled/unlocked text box, so it can be amended whether empty or populated.

Is there any way of doing what I have been asked? If you know the answer, please treat me like I'm an idiot and make it very simple to understand!!!

Many thanks for taking the time to read this.

Regards

Ed
 

A:Solved: Access 97 forms

Ed, welcome to the Forum.
About the only way to do this is with VBA code.
So the first place to work with is the "On Current Event Procxedure" of the Form.
With the Form in Design View click on the Events Tab, then on the "On Current" event and then select Procedure from the drop down that appears.
3 small dots should also appear, click on those to open the VBA Editor at the correct Event and type in
if isnull(me.fieldname) then me.fieldname.enabled = False

Where fieldname is the name of the field that you want disabled when it is Blank.
The VBA editor should offer the control names after you type in me. and as you type in the other characters jump to those available with those letters.
See if you can get that to work.
 

2 more replies
Answer Match 62.16%

I have a form I am trying to not go to a new record until certain fields have data entered by user. I have the following code, but whenever I use Access's New Button (at bottom of form), it will still go to a new record without data entry in the desired fields. These fields can not be required in the bable because it's based on a selection.
Example If user selects "House", only certain fields need to be entered, if user selects "Condo" only certain fields need to be entered. This is just an example. Below is the actual code I have so far. ----Thanks to all who can help me!!!

Private Sub Form_AfterUpdate()
Dim strUserInput

If Me.Contract_Type = "House" Then
If IsNull(Me.ITB_No) Then

strUserInput = InputBox("Please enter ITB number")

'Check to see if there is any entry made to input box, or if
'cancel button is pressed. If no entry made then exit sub.

If strUserInput = "" Or strUserInput = Empty Then
MsgBox "No Input Provided", vbInformation, "Data Required!"
DoCmd.GoToControl "ITB_No"

Exit Sub
End If

'If input entered, continue
'If input not entered, give message and exit sub

If strUserInput Is Not Null Then
Me.ITB_No = strUserInput

Else
MsgBox "You MUST enter a number", _
vbOKOnly, "Important Information!"
Exit Sub
End If
End If
End If

End Sub
 

A:Solved: Access 03 - VBA, Forms

6 more replies
Answer Match 62.16%

Hi,

I need a bit of help as I have no idea how to go about even starting this. I would like to have a drop down box (which is the part I can do), but then depending on what was selected from that box, I would like to have another drop down box appear if a certain something was picked from the first. Is it possible to do this in just SQL? Can somebody point me in the right direction? ...OBP?
 

A:Solved: Access Forms

16 more replies
Answer Match 61.74%

Hello all,
I have a db where I have a list of projects. The projects are separated into two tables with the first containing information about the client and a related table with information pertaining to the individual projects done for each client. This is a one to many relationship.

I am currently trying to establish a resume creator for my db where I would have an employee table which would be related to individual client's projects. My goal is to create a form that I can select what projects each employee worked on and give a report on all the information given in the client and project tables for the projects any particular employee worked on.

What I cannot figure out is how to relate the employee table. I can't do it to just the client table because the employee is not on every project the client has and I cannot relate it just to the project table because I need the client information in my report as well.

Any help would be greatly appreciated,
Madctch
 

A:Solved: Access: Bind to multiple fields

6 more replies
Answer Match 61.74%

I have multiple fields in my table, two of which are called Customer_ID and Supplier_ID. They are just text fields. What I need to do is make sure only one of them is filled in each record. In other words, how do I constrain 2 fields to keep one from being filled when the other already has something in it? I'm using access 2007 on windows xp.

Thanks!
 

A:Solved: Table fields, one or other, Access 2007

8 more replies
Answer Match 61.74%

I have one table that has information on all my gears, called tblGearFeed. This table has 3 columns of information (CAM, Feed, and Gear A)

I am using a form called frmQuotation.

I have "CAM" and "Feed" fields that users will fill in. Based on these two fields, I need the "Gear A" field to lookup the cooresponding data from tblGearFeed.

I have been trying to use the following function:

=DLookUp("[Gear A]","[tblGearFeed]","[CAM]=Forms![frmQuotation]![CAM] And [Feed]=Forms![frmQuotation]![Feed]")

This formula is not working.
Can anyone guide me as to what I am doing wrong?
 

A:Solved: Access- DLookup based on two fields

If you do an AND it will only bring up the values in each field that equal your parameters. Use an OR instead.
 

3 more replies
Answer Match 61.74%

Ok...I know there is a simple way to do this but can't come up with it.

I would like to check multiple Combo Boxes (40) to see if the same value has been entered. In the example below it is "ClassEquivalent". (The same code would work for a NULL value) If the value is True then I would like to display a msgbox. I know that there is more simple code than this...and I only want one Message Box to be displayed if any of the Combo Boxes contain that value.

If Me.ComboBox1.value = "ClassEquivalent" Then
MsgBox("Please Fill in Type of Equivalent",vbcritical,"ERROR") =vbok
Me.LabelComboBox1.forecolor = 255
Else
If Me.ComboBox2.value = "ClassEquivalent" Then
MsgBox("Please Fill in Type of Equivalent",vbcritical,"ERROR") =vbok
Me.LabelComboBox2.forecolor = 255
etc.....
 

A:Solved: MS Access: Check Multiple Fields for One Value

There is a set of videos here that show you how to unlock and lock a group of controls, from a basic method setting each control individually, right up to using a class module that monitors the command button and takes action. I think you could adapt this code to work for your 40 Controls. The videos about the class module method are the videos numbered 5 6 and 7.

Cheers Tony
 

2 more replies
Answer Match 61.74%

Experts,
Usually iin MS ACCESS 2010 when you create a field in a table for attachement, the sub fields are there in the " Add Fields" top a form but this time after entering the field of Attachment in a table , i dont see any of the subfields. Did I miss something here.
Your help is appreciated.
 

A:Solved: MS Access 2010 sub fields attachment

Sorry, I have never used Access 2010 or it's Attachments field.
 

2 more replies
Answer Match 61.32%

Hi,
I have this problem and can not see that it has come up before on here.

I have a form. When I click a command button, a report opens. When I close the report, the form reappears but it is NOT maximized. Is there a way that I can make sure the form remains at maximized size even when the report closes?

I use docmd.maximze when the form and the report opens. When the report closes, I use docmd.restore in the close event.

I can get around it by placing an openform part in the report close event but I need to use the same report in various forms and do not want to do this.

Can anyone help?

Thanks
Simon
 

A:Solved: Access Forms Maximize

Simon, some things to check, ensure that the form property modal is not set to yes, that the Control Box and Max/Min Buttons are set to yes.
I also use the Maximise command in the Autoexec Macro after opening the first form.
 

3 more replies
Answer Match 61.32%

I have a comments field, but when I press enter, it goes to the next field. How can I change it so that, when I press enter, it goes to the next row in that field?

I also want to know if I have a field with two different states listed, how can I select one by pressing enter?

Thanks.
 

A:Solved: Microsoft Access - Forms

8 more replies
Answer Match 61.32%

Hi,

I'm hoping for some advice on Access application architecture.

I'm modifying an application in Access 2003 on XP. I have a form that's bound to a (linked SQL Server) table and I want to alter the way the form stores modified data - specifically, I want it to call a stored procedure in the DB instead of automagically altering the underlying data through the binding mechanism.

I tried binding the form to a SELECT query instead of the table, hoping that this would break the binding for storing the data as well, but it did not.

So, what is the best way to change the form so that it reads the data from the table but requires action (either an UPDATE or call to a stored procedure) to update the DB? Currently, even if I comment out the "DoCmd.DoMenuItem ... acSaveRecord", on the forms save button, the record still gets updated.

Thanks in advance for any help you can give me.

I'm a reasonably experienced developer, incidentally, but this is the first time I've delved into Access.

Regards,
Bob
 

A:Solved: Access forms binding

16 more replies
Answer Match 61.32%

I have a problem with Access. I am trying to add a command button to a form that opens another form filtered for 2 fields from the original form. I can get it to filter for one field inthe wizard but how do I get it to filter for 2.

Also how do I open a subform with a command button and have it ready at a new record with a certain number in one of the entry fields...

Am I making any sense??

Steven
 

A:(Solved) Help with forms in Access 2000

To have two conditions in the Where clause, use the word AND. For example:

"[Field1] = " & [Field1] & " And [Field2] = " & [field2]

IMPORTANT! Remember the space before the word AND or it might not work. (It might attatch the word to the comparison data from before instead of leaving it as it's own word.)

If you want to open a subform with a command button, I would reccomend setting the Visible property of the Subform Control to false by default. The following code should be put in the Click event of the Command Button:

<BLOCKQUOTE><font size="1" face="Verdana, Arial">code:</font><HR><pre>
Private Sub NameOfCommandButton_Click()

With NameOfSubformControl
.Visible = True
.form.recordset.addnew
.form!NameOfTextBoxOnSubform = ValueYouWantToGiveIt
End With

End Sub
[/code]

That should do the trick.

Good Luck!
 

2 more replies
Answer Match 61.32%

I have a very basic one table database in Access 2002, which users only use as a store for data. They view the database from a form and perform searches ad hoc using the form filter. I would like to enable them to produce a report of the filtered information to be printed as required. How would I go about this?

I imagine it will be some sort of command button, but none from the wizard seem to suit so if I have to write the code....where do I start? I can do code but no idea on the structure of it to achieve this.

Thanks in advance!
 

A:Solved: Access Reports from Forms

16 more replies
Answer Match 61.32%

Hi there,

I have a subform within a form that contains a field with the control source =[PurchaseOrderNumber] & [PartNumber] which basically puts two fields together to create a barcode. I would like for this value to be saved into a field in a table.

Does anyone know how I would go about doing this? I have searched for solutions & have found none.

Please let me know. Thanks!
 

A:Solved: Access 2010 Forms

6 more replies
Answer Match 61.32%

I think the solution to my problem is fairly simple, but I can't seem to find it in any forums on the web(probably because I don't have the lingo down).

So here we go: I've created a parking pass database for our organization. The primary key is attached to a 6-digit parking pass number. I want to create a search tool so our parking guys can enter the parking pass number and retrieve the corresponding form. The form has a nicer layout than the table, so I want to retrieve the form, which contains car description, owner, address, etc.

Is this possible?

Thanks in advance, you guys and gals are very helpful
 

A:Solved: Search through forms in Access

15 more replies
Answer Match 61.32%

Can someone please tell me how to I create a form that will have a check list for safety procedure? This form will need to be able to allow a new record but save the old record. I am adding a safety checklist in access 2010 for my job and I need to know how to do this. Thanks
 

A:Solved: Access 2010 forms

6 more replies
Answer Match 60.9%

I'm making a simple SELECT query in Access 2007, but I need to only select those records where one of the fields is LIKE the text entered into a text field on a form. Hitting a button next to the text field executes the query.

The problem is that when I "build" the criteria in the query, it doesn't seem to want to work when I throw in the wildcards around the field name. For instance, if I wanted to search for records with a field like "simple" then I'd put stars around simple inside the quotes like this "*simple*". That works perfectly fine. But if I try to make that criteria dynamic based on the text entered into the text entry box, it doesn't want to work: "*[Forms]![SearchForm]![SearchText]*" . It only works if I take out the stars and quotes: [Forms]![SearchForm]![SearchText].

Any help? Maybe I'm just not using the correct syntax?

Note: I know how to get this to work in VBA, but I need it to work in pure SQL without the help of string concatenation.
 

A:Solved: Access 2007 - Referencing text fields in SQL... possible?

No worries, I found a work around that doesn't involve anything dealing with this question. Answer it if you wish, but the only reason it's still listed here is because I can't figure out how to delete it.
 

3 more replies
Answer Match 60.9%

Ok I have been struggling on this one all morning.

What I am tring to do is this.

I have a combo box, when the name is selected in the combo box, I would like it to auto populate the Addres/City/State/Zip of the persons name.
 

A:Solved: Access - Combo Boxes and Text Fields

16 more replies
Answer Match 60.9%

I am trying to run a query to return the following criteria:

I have a table with columns 1 through 5. Some fields are empty in different columns. I want a query to be able to say if there is a blank in column 1 return record to query, if any blanks are in column 2 return also to query, etc...

Col1 Col2 Col3 Col4 Col5
X blank x x x
blank x x x x
x x x x x
x x x blank x

In this example the query would return only records 1,2, and 4. I can get it to work for only 1 column in a query not multiple columns with blanks in a query. Do I have to run multiple queries get get the data?
 

A:Solved: Access table with blank fields in different columns

6 more replies
Answer Match 60.9%

I made some changes to the references in Visual Basic, Tools, References in Access 2007.
Now when I create a query in Access 2007 using the QBE grid, the query returns ALL fields from all tables regardless of how many fields were selected in the grid.
The SQL view shows an extra space and * at the end of every SELECT statement, like this:
SELECT Customers.CompanyName, *
FROM Customers;
How do I stop these unwanted extra characters from appearing in my query?
 

A:Solved: Access 2007 queries return too many fields

The solutions turns out to be: Access Options > Object Designers > Query Design > (uncheck the "Output All Fields" box). I'm still not sure how this managed to get checked initially.
 

1 more replies
Answer Match 60.9%

I am designing a database in access and would like to know how to design a macro or query with a check box would perform the following. In the form when checked it would allow only those records, when checked, to be printed.
 

A:{SOLVED} Access 97: Printing Checked Y/N Fields with YES criteria

7 more replies
Answer Match 60.9%

YSB, I really tried to figure this one out on my own. I have looked through the indexes of two books, searched through my Access links, even did a search on a Access UBB and still come up with nothing. It seems like it would be a common enough control I am really surprised that the solution is eluding me.

Ok with that out of the way, How do I compare two fields and pick the greater of the two values?

Form
[rate1] ,[rate2] , and [unboundfield]
The unboundfield's control source should select whichever value is greater.

The IIF function is not covered very well in my Mastering Access book so I am not sure if that is what I need or not. It seems similar to the conditional control iif([total]>0,[total],[EXTRate])that I have tried to convert that one to work.
------------------
Building the Ultimate site list for PC support.

[This message has been edited by Talismanic (edited 06-28-2000).]
 

A:(Solved) Comparing two fields and selecting the greater in Access 97

DOH!!!

This seems to work =IIf([rate1]>[rate2],[rate1],[Rate2])

Thanks anyways!

------------------
Building the Ultimate site list for PC support.
 

2 more replies
Answer Match 60.9%

Hello,

Using mixture of Windows XP with Office 2003 and Windows 7 with Office 2013 (so the database is in Office 2003 format).

We have a database showing companies that we have spoken to in regards to working with us. Each company will have shown interest in covering several postcode areas.

I have created a "Yes / No" (tick box) field for each postcode area ("AB", "AL", etc). A total of 121 fields.
I have also created a combo box "PostcodeFilter" which works from a table with all the Postcodes in (matching the 121 fields)

Google has brought up some code that works "On Exit" from the combo box that filters the records...

Me.Filter = "EquipmentID = '" & Me.ComboName & "'"
Me.FilterOn = True

This would look at a single field (in the example "EquipmentID") and filter where this equals what has been selected in the combo box.

What I need is that it would look at whichever field is named the same as selected in the combo box and filter where that field = Yes (ticked)

Example: Select "AB" in the combo box and it will filter the records to only show when the field "AB" is ticked.

Is this possible?

TIA
SBF
 

A:Solved: Access: Filter using Combo box and multiple fields

This is not quite as straight forward as the Google example because you have to convert the combo selection to a field name.
I had the code to do this a few years ago, but I don't think I do now.
I will see if I can recreate it.
 

3 more replies
Answer Match 60.9%

I am looking to have an Age field in my table that automatically calculates the age using the Date of Birth field value. I tried putting DateDiff("yyyy",now(),[Date of Birth]) in the Default value of the age field but never worked. Any ideas?
 

A:Solved: Access Table fields default values

Ya I don't think you can do that. You need to try and use queries for you calculations, you bring the table into the query design and then add the calculated field. This query can then be used in forms and reports (with your new field).

I made up this expression, there could be shorter ways of doing it...but the way you had it, only gives you the Rounded Year value (the age they will become in the current year), which might be incorrect for what you are doing. When you are listing their AGE does it have to be exact?

eg if born Dec 31 1967, then when viewing their record on Jan 1 2008, they will appear to be 41 years old, but their birthday is not for another 364 days?

If you don't care then just use you expression in the query, otherwise use this one. I made it break down the Year and Months, but not days because it is variable (leap years).

Age: Int(DateDiff("m",[DOB],Now())/12) & " Year(s) " & (DateDiff("yyyy",[DOB],Now())*12)-DateDiff("m",[DOB],Now()) & " Month(s)"
 

3 more replies
Answer Match 60.9%

A friend was trying to show me how to use Access today and she said one thing I could easily do is create an access file with all my addresses in outlook (which I could later use to make forms). I have the addresses exported to CSV but there are businesses (of varying categories - legal related, business related, customer related, etc) and there are individual customers. The businesses, naturally, don't have any names filled in for the first name/last name categories in Outlook, and my friend said she *thinks* this may cause issues when importing to Access because it sorts alphabetically. Will this be a problem? She said I could create multiple versions of contact lists but that sounds a bit complicated (with some businesses falling into multiple categories, for instance) and as she said herself she's a bit rusty and she just thinks it might be a problem, I wanted to check before proceeding either way. Thanks and I hope this isn't too vague!
 

A:Solved: Access - importing a CSV file w/blank fields

6 more replies
Answer Match 60.9%

I have a Contact Management database built in Access, and am wondering how I can let the user hyperlink to a contact's website from a Form. I have "Contact Website" as a field. I've figured out the smarttab to send them to Outlook from the Contact's e-mail, but not so much luck on the www. address.

I'm a relative newbie to Access, so this may be a simple one, I don't know. Inserting hyperlinks in Excel or Word seems much easier!

Thanks!

 

A:Solved: Access Forms - Inserting Web Hyperlink

Just choose Hyperlink as the data for your table field.
 

2 more replies
Answer Match 60.9%

I saw some past threads about creating search/filtering forms in Access 2010, but I'm still having some trouble.

My database has several tables which I've combined in a union query, and I'd like to create a form that will basically search a user name and show me all of the records associated with that name in a subform. So far, I've got an option box for user name, but the subform only shows me a list of all users, not just the one chosen in the option box.

How do I get the subform to list only those records associated with the selected name?

Thank you very much for your help!
 

A:Solved: Search Forms in Access 2010

12 more replies
Answer Match 60.9%

Hiya

Finally trying to do something for work, and one thing I was thinking about for ages is a database.

So, created one in Access 2010 (at home). It has a table1, and then two forms.

Only been working on it today, and very new to Access, so its trial and error time for me

Anyway, most of the Text Boxes I think are okay being unbound, as each form has its own entries.

However, there is one that I would like linked. Its one like a certain number you designate for a particular item (not a sequential number).

As there are two forms, can you easily link one form to the other, for the one field?

The box is Text7 on the form I would like Text23 from the first form I would like to be linked.

Looked at Control Source, typed Text23, went to View Form View, and then tapped some numbers into Text23 on the 1st form. Didn't seem to work, so I bet I'm missing something very simple
I can grab screenshots, but the pc I'm doing it on has issues, so cannot go onto webpages (using a laptop to type this).

Thanks

eddie
 

A:Solved: Access 2010 - Linking 2 forms together

7 more replies
Answer Match 60.9%

I'm sure this is pretty simple but I can' seem to get it to work. Here's what I have....

Main Form is where the data entry occurs. really can't be used to search records

Form 1 - is a variation of Main form but is queried to return only certain criteria. IE... only pull records that are missing data.

Form 2 - allows searches of all records in the database.

Currently when I open a form they stay open, so they are not refreshing. So would it be better to somehow have each form close when I change forms or somehow do an event with requery? There are many fields that have to be updated from either just making a change to a record or adding a new record once all three forms are open.....

Any thoughts?
 

A:Solved: Access Event to Update Forms

I am not sure that you need 3 forms to do this.
 

3 more replies
Answer Match 60.9%

Hi,

This is my first post here and is quite new in Access. I have searched for various sources of information but I still do not quite get it. I came upon this forum by chance, and hope that you guys can help to solve some of my doubts.

1. Say, there are these 3 tables - Inspector (One who do the inspecting), Audited_Form (The form used to audit operators), Flight_Audited (Flight which they audit on)and Operator (One who are being audited).

Hence;
Under Inspector, the fields are InspectedID, Name.

Under Audited_Form, the fields are FormID, InspectedID, OperatorID, Organisation, Date, Time, Questions, ...etc

Under Flight_Audited, the fields are FlightID, Flight_Details, Date, OperatorID
(Both the "date" in the above 2 tables are the dates the inspector do the auditing)

Under Operator, the fields are OperatorID, Name, Organisation

1 inspector can used many forms to audit on 1 operator. 1 operator can be associated with many flights.
(I hope I did not make you guys confused, will try to make a print screen asap as the database file is not with me currently)

In this situation, does the ID serves any purpose other than as a unique key? For example, if my supervisor now keying information, does he need to look up for which inspector is which ID?

2. If i were to create a form for easy keying of information, how to input multiple tables? I've looked up the web, and it actually mentioned about subform. I tried, but I failed.

How do I create a form that when... Read more

A:Solved: Various Questions about Relationships and Forms in Access

16 more replies
Answer Match 60.9%

I have developed a simple Access application for sharing on our network. My computer has a screen resolution of 1400x1050 while others are 1028x768 causing my forms to "overflow" their screens. I like my forms for those of us with 1400 resolution and can make tabbed forms for those with 1024.

My data is in a back end and users access it via a front end.

My question is what is the best way to handle this? For example, I could write another front end (called "front end 1024") and have users with 1024 screens access the data via that or I could include them in the same front end and have users choose each time. Alternatively, is there a way for Access to know which forms to use for each person? Note that right now I do not have any security or users identifying who they are to log in so I'm not sure the application "knows" who is logged in.
Thanks.
Dennis
 

A:Solved: MS Access Forms and Screen Resolution

6 more replies
Answer Match 60.9%

I have experienced this recently in several databaes. A single form will suddenly quit opening. If I click a menu button or if I open to the forms list and click on it there, it won't open and the whole database pulses like it is wanting to give me an error message. I find that if I open the form in design mode then switch to form mode only will I be able to use it. I have closed and reopened the program, I have created new buttons, and tried renaming the forms but the problem persists.
 

A:Solved: Access 2010, forms won't open

8 more replies
Answer Match 60.9%

Hi to All!

Is there a code that will give me the record number and total number of records in a form?

I want to create a control that shows the record # of the total records. I don't want to use the controls Access provides at the bottom of the form.

Thanks to All who can help!
 

More replies
Answer Match 60.48%

I have a field that I want to show the total of 10 fields added together. These 10 fields have their own calculation of 2 fields added together.

Example:

Total1= Rate * Days
Total2= Rate * Days

Grand Total = Total1 + Total2

I want a Grand Total of all these into cboGrandTotal

I built the exression on the control source of cboGrandTotal as =[cboTotal1]+[cboTotal2] etc.

This does not work. I just get an empty Grand Total.

Can someone help? OBP?
 

A:Solved: Calculate Total w/Calculations in fields, Access 2007

12 more replies
Answer Match 60.48%

Hello -

I have a data table that is recording inspection dates and inspection purposes for specific records. There are a total of six occurences (i.e. Inspetion Purpose (1), Inspection Date (1), Inspection Purpose (2), Inspection Date (2)).I would like to use a form to input data into the fields. Is it possible to build an expression so that the data input into the form inserts the data into the table with the first available null value for that record.

In other words, if there is a value present in Inspection Purpose (1) & Inspection Date (1) then the data from the form is inserted into the fields for Inspection Purpose (2) & Inspection Date (2).

I would greatly appreciate any help!
 

A:Solved: Access: Inserting Data from a Form Into Fields in a Table

10 more replies
Answer Match 60.06%

Hi,

I'm trying to format checkboxes (and textboxes and combo boxes - both of which I can do), in an Access 2013 form, so that they are effectively highlighted when they receive the focus.

Text and combo boxes are easy beacuse I can just change the .backcolor format, which is what I want but checkboxes don't have that property, what can I do to get the same effect with them (not that changing any of the colour properties seems to actually make any difference)?

Many thanks,

Richard
 

A:Solved: Access forms - checkbox control formatting

As you have found you can't manipulate it directly.
So you could manipulate it's Label or you could do the following.
Reduce the Check Box's Border so that it just surrounds the check box.
Add a Rectangle around the checkbox, send it to the back.
Using VBA change the colour of the rectangle's background when the checkbox is clicked or gets focus.
 

2 more replies
Answer Match 60.06%

Here's my problem. I'm creating an evaluation form. I have a parent form that captures the Project Information data, then 5 subforms, each containing the same set of fields (Requirement, Score, Comment etc), but geared around 5 different categories (5 phases of the project which each have their own requirements(questions).
My problem is when I create records in 2 child forms, the next time I open the form for that parent record, I see all the child records copied under each of the 2 child forms.
An evaluation is rarely completed in one sitting, so I need the records to be kept divided in the form view.
I have used 1 table to hold all the child records, and used queries to divide them for the child forms. So I am assuming the problem is that all the children live in the same table.
I tried creating separate tables for each of the 5 categories, but then I can't get the relationships to work between the parent form and the child records.
Can you suggest a table/form/query structure that might solve my issue? I can upload my file if needed.
 

A:Solved: Parent and Child forms in Access 2010

8 more replies
Answer Match 60.06%

1) Using If [Field2] = 0 Then Exit Sub
[Field3] = [Field1] / ([Field2] * [Field2])
as an After Update event procedure on Field1 and Field2, Field3 is not being updated as data is changed in Field1 or Field2 - but only updates once I have gone to the Next record, then back again.
(What I have done above works perfectly on another database, which is why I can't figure this out. i.e. enter data to Field1 and tab - Field3 is updated, same for Field2.)

2) In this database, I have Date Of Birth entries. I would like to automatically display the current age for each entry on the input form. So I will have Name, Surname, Date Of Birth, Current Age - with Current Age being calculated automatically.

Assistance always appreciated.
 

A:Solved: Access Forms - Event Procedure and Age from Date

1) It appears that I had a different event procedure running along side this which was causing havock, working fine now.

2) On Entry Event Function:
If [DOB]=0 The Exit Sub
Age=Year(Now())-Year([DOB])

--- All working well ---
 

1 more replies
Answer Match 60.06%

Hi all. I am sure this is a fairly basic question which shows a lack of understanding of how Access works, so if its stupid, I apologize in advance...

I used the form builder to create a new input form for data. There is one selection for "Manager", which defaults to a text box that you can navigate with the arrows on the bottom of the sub-form, which has the manager id (primary key), the date, and the return associated with it. It works as intended, but the text box won't work long term.

I expect to have a decent number of managers, so I'd like to make that a drop down box so the user can select it, not have to scroll through one by one. I changed it to a combo box, and specified the query to populate the box. When I select a different manager than the default, I get an error which says, "The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again."

I think its trying to create a new manager with the selected name from the combo box, but it won't allow duplicates (which it shouldn't). What am I missing conceptually here?
 

A:Solved: Access Forms - Changing Text Box to Drop Down

16 more replies
Answer Match 60.06%

All,

Ok, Here we go again. I have another very basic DB, that i have plans for later. Right now I just have one tbl/qry/frm/rpt. In the report I have some static labels, and I would like them to be visible/invisible based on data being present or not. I have attached the db. The code I thought would work is below. Let me know where I am going wrong if possible. Thanks
Code:
[SIZE="3"]Private Sub Detail0_Format(Cancel As Integer, FormatCount As Integer)

If Me.Wk_Zn_1_8_Tickets = True Then
Me.Label147.Visible = True
Else
Me.Label147.Visible = False
End If

If Me.Wk_Zn_9_15_Tickets = True Then
Me.Label152.Visible = True
Else
Me.Label152.Visible = False
End If

If Me.Wk_Zn_1_5_Tickets = True Then
Me.Label153.Visible = True
Else
Me.Label153.Visible = False
End If

If Me.Wk_Zn_6_12_Tickets = True Then
Me.Label154.Visible = True
Else
Me.Label154.Visible = False
End If

If Me.Wk_Zn_13_15_Tickets = True Then
Me.Label155.Visible = True
Else
Me.Label155.Visible = False
End If

End Sub
[/SIZE]
Let me know if you can help.....Thanks.

Nuschool33
 

A:Solved: Hiding Labels based on data in Access Report

11 more replies
Answer Match 59.64%

Hi there,
I have an MS Access database (2000, but primarily operating on PC with Access 2010). The database logs wagons coming to and from our site. Mostly, these wagons are in and out in the same day, but it is possible for them to park up overnight, for which we charge a small fee.

The situation has now changed slightly, in that we now accept wagons staying on site for longer periods (usually a weekend), so I've been asked to introduce an Exit Date field as well, and correct the existing calcualtion of time on spent site.

Currently, the following fields appear in the main table (called Stays):

Arrival Date
Arrival Time
Exit Time

There is also a field called Duration, which (for some reason) is autofilled by an Update Query, with the Update To value set to:

IIF([Arrival Time]>[Exit Time],1440+DateDiff("n",[Arrival Time],[Exit Time]),DateDiff("n",[Arrival Time],[Exit Time]))​

This is clearly inaccurate - it assumes that if a wagon's exit time is earlier in the day than its arrival time, then it must have stayed for 1440 minutes + the (negative) difference between the two times, and if the exit time is later or equal to the arrival time, the wagons has been in and out of site in the same day.

As wagons could be on site for multiple days (so the 1440 mins figure could be wrong) and could leave x amount of days plus x amount of minutes after arriving (so the assumption that it's exited the same day as it arrived could be wrong),... Read more

A:Solved: MS Access modifying a date and time calc in separate fields

8 more replies
Answer Match 59.64%

I have created a form using the Wizard.
I selected thirteen fileds from one table
And thirteen from another table
After I spent a lot of time developing the form, I found out I needed another field.
I added a new field to the one table, but now how do I create a Bound Box to this field in my form?
When I try to use the Field list button all I get is the original twenty six fields to choose from.
Maybe there is a way but How do you add more fields to the field list?
 

A:Solved: Access/Adding Bound Text Boxes to Forms

9 more replies
Answer Match 59.64%

I have a database with approximately 15 tables and forms. There is a form created for every table. When a form is updated, I want the other forms with the same fields to also automatically update without having to re-enter the information in a different form. Is this possible? Please advise.

Thanks,
Tonya
 

A:Solved: Access 2007: Forms for Data Entry-Update

8 more replies
Answer Match 59.22%

Hi,

I have used various answers on this board to set up a form in access 2007 that includes a combo box that when changed, auto populates 2 other text boxes in the form. I used the code builder to this. It works fine.

HOWEVER...the information is not being saved in the individual records. When you reopen the form, those 3 fields (the combo box and subsequent text boxes) in the past records are blank again. They all change to whatever is chosen in the NEW record.

Any idea on what I may have done incorrectly? I have been using Access for one week, so I am sure it could be one of a million things I did wrong!

Thanks
 

A:Solved: Auto populated form fields in access 2007 not retaining the information

JacklynKy, welcome to the Forum.
It sounds like you have no "Control Source" for your Combo box, this means it has no field associated with it in the table to store the selection.
 

3 more replies
Answer Match 59.22%

Hey there folks! I had an issue with clearing fields and that was resolved but the resolution presented another problem. A problem which I have been unsuccessful in resolving on my own.

I have tried about 3 different methods using command buttons and the VBE but nothing is working.

I need 2 buttons: One to clear all form fields and one to clear all form fields then close the form.

I know how to make a button to close a form but I am unable to make it clear the fields prior to closing. I have also been unsuccessful in creating a button to simply clear the form fields.

Any help is greatly appreciated.

Jason
 

A:Solved: MS Access 2007: Creating a 'Clear All Form Fields' button that actually works

11 more replies
Answer Match 58.38%

MS Access:
I have several fields in a form that uses two tables. I would like to set up a command button so that when you are on a current record, it will copy the field information from the record you are on to other fields in the same record. I do not know the SQL commands to set this up.

For example, there are name fields, address fields, and phone to be copied. Reason: One is contact information; one is survey information. In most cases the information is the same but on rare instances, the information needs to be entered (when different).
This is not an update for all records in the database. This is based individually on each record.
Not sure if you need to do On current, on click, or after update.
Not sure if default value in form can be used.
Please help.
 

A:Copy data fields in same table to other fields in same table MS Access

16 more replies
Answer Match 56.28%

I have developed a form in word 2003 with drop down boxes. I want users to complete mandatory fields before progressing to the next field. I understand that this is activated by a macro. I am unsure how to do this. How do I start the process so that a form cannot be completed without the necessary fields being answered. Please try to keep the answer fairly simple with examples if possible.
Thanks
 

A:How to set up mandatory fields in Word 2003

Welcome to the forum. I am not good with VBA but it would be including an If>>Then>>Else statement to test the object to see if it is null or blank. You would probably want to it do it before you save and then have it select the objects that are not filled in correctly. I am posting this in the hopes one of our VBA people will respond to it. :0
 

1 more replies
Answer Match 56.28%

I've lost all email fields from Outlook and I know how to add them back for each folder but is there a way to do it for all folders at once. I have no idea how this happened and I have a lot of folders so adding the fields back one folder at a time is going to take some time.
 

A:Outlook 2003 lost all fields

Possibly:
http://www.techrepublic.com/article/apply-global-custom-views-in-microsoft-outlook-2003/5243615
 

2 more replies
Answer Match 56.28%

I have set up a .dot file which uses reference fields. I have set up the input field as a drop down box with bold text.

My query is when the ref field populates the first word is bolded the second one is not.

Can anyone assist?

Cheers!
 

A:Reference Fields - Word 2003

11 more replies
Answer Match 56.28%

I have created a custom form for telephone messages in OUtlook 2003 and it contains user defined fields such as "Caller Name", "Company" etc.

The form works great... the only thing is that when the recipient receives the email message, they only see all of the details if they open the message.

Most of my colleagues use the reading pane to review messages and these fields do not display in the reading pane.

My question is.... is there any way I can make my form fields to display in the Reading Pane so the message does not actually have to be opened.

Any help will be much appreciated.

Thank You!
 

A:Outlook 2003 forms

The problem is that the form is probably being held locally on your PC. If it is loaded onto the server then it should work perfectly fine. I have seen this before when I tried to setup a phone message form for former users of Lotus Notes and Groupwise.
 

1 more replies
Answer Match 56.28%

I am making a form in outlook 2003 and what I am trying to do is when you check the check box to enable the text box but i haven't found the way to do this. And it looks like the little I know of Visual basic won't work with outlook.

Also is there a way to grab the computer name and place it in a text box?

I am placing the code in the Script Editor.

Thanks
 

More replies