Computer Support Forum

Solved: Access Auto-Populate a Field using a field from another table

Question: Solved: Access Auto-Populate a Field using a field from another table

I have a table titled time_slips used to track the amount of time an attorney spends with a member. In the time_slips table there is a field titled reference_id which is a combo box pulling it's displayed information from another table titled references. Also in the time_slips table is a field named details which is native to this table.

My question is, can I have the details field auto-populate "no charge" when a certain reference id is selected (i.e. 300)?

Thanks for any help you can provide.

Relevance 100%
Preferred Solution: Solved: Access Auto-Populate a Field using a field from another table

I recommend downloading and running Reimage. It's a computer repair tool that has been proven to identify and fix many Windows problems with a high level of success.

I've used it in the past to identify and fix everything from blue screens (BSOD's), ActiveX errors, corrupt files and processes, dll/exe/sys errors, recover lost memory, Windows update problems, defragging, malware removal etc.

You can download it direct from this link http://downloadreimage.com/download.php. (This link will automatically start a download of Reimage that you can save to your computer.)

Answer: Solved: Access Auto-Populate a Field using a field from another table

15 more replies
Relevance 117.16%

Good afternoon all!

I am working on writing a Security Incident Report program, and I am running into a problem. I have a Date/Time field, which I am attempting to auto-update from another Date/Time field, based on the result of a Yes/No field input. Is this even possible? To be specific, the user enters the date of the report. On the next form page, it asks the user if the report date is the same as the incident date. If the user answers Yes, I would like it to enter the data from the report date field and grey out the option to enter the new date. If the report date is different from the incident date, the user would then be required to enter the date of the incident. This takes place three different times on the same form, based on the incident date, date responders cleared from the incident, and the date the incident was placed under control. I am running Access 2007. Thank you!
 

Answer:Creating Access 2007 Auto-populate field based on different table Yes/No data

GrahamTechnology, welcome to the Forum.
Yes it is using VBA.
 

3 more replies
Relevance 102.95%

Hi All!

I have a form that calculates an employees current age in a Text box (based on today's date and their date of birth). I created the form to act as an 'entry' form for new employees in an HR database.

I have a field called 'age' in a table that I would like to populate with the result of this formula. How can I do so when the 'Control Source' property of the text box on the form is holding the formula!? That's where I would normally link it back to the table!

Thanks in advance for any help!

Cheers

Ty
 

Answer:Can an access table field populate from a calculated cell in a form?

You use simple VBA code in the Date of Birth field's After Update Event Procedure and set the Age field's Control Source to the table field to store the data.
 

1 more replies
Relevance 101.21%

Hello,
i am in the process of creating database and i have an issue i am trying to resolve.

On my main form i have a list box where the user choses a binder number. The binder number, alond with the binder name and binder location is stored in table, binders. On my main form i also have a filed called Location. What i need is for the location field to be filled with the location of the binder that was chosen from the list box mentioned above. I know this can be done i am just having trouble figuring out how to do it. I know it involes a query but i am not sure how to set it up. I am an advanced begginner with VBA so if you reference code, i will probably be able to figure it out...

Thanks in advance!
 

Answer:Access Quesiton Auto Populate a field

16 more replies
Relevance 95.12%

Please help

I have 2 tables:-

1 Master Input Table.
1 Job Lookup table - (contains Job Number and Name)

When in-putting job details into the Master Input table i would like to enter the job number only and have the job name appear automatically without running a query, much the same as a 'vLookup' command in excel.

Is this possible?

Can anyone help?
 

Answer:Auto Fill In MS Access Table Field

Yes it is, the easiest way is to have the Job Lookup Table as a Sub form on the master form (Master Input Table) linked via the Job Number.
Or you could select it from a combo box, (no typing).
 

1 more replies
Relevance 93.96%

Hi All,
I need an help in creating auto number field in access database. Let me explain you further. I have an access database which 20 million records. When i am trying to add auto number field i am getting error "File sharing lock count exceeded".
Then i did some google search and got some infomation like editing the registry file , in my case its not possible due to security restrictions.
And another option of adding a code in VB immediate window also i tried but this option is also not working.

"DAO.DBEngine.SetOption dbmaxlocksperfile,25000000"
Can anyone help how to solve this issue else can you please help me on how to auto populate the numbers in a specific field using VBA codes.
Thanks in advance.
 

Answer:Auto number field in a Access database table

16 more replies
Relevance 89.9%

I think this is a simple question, but not sure.

I want a field in a form to auto populate as the user types into it. For example, as they type "J", the first name that begins with "J" comes up. As they type "Jo" the first name with "Jo" comes up, etc. That part is basic. I am using a combo box and it works fine.

The next part is the tricky part. If the name does not appear in the list, is there a way to add it as a record in the related table?

Actually I created a simple table (only one field) just for this purpose, so if there is a trick on the field control level, to add the new entry to the combo box list that would be fine.

I don't think I can use a sub form because it won't give me the "auto populate as you type" part, right?

I also found some code on the MS help menus that may do it, but I am not a VBA programmer so I am not sure but here it is:

Private Sub DoctorName_NotInList(NewData As String, Response As Integer)

Dim dbsIPutMyDatabaseNameHere As DAO.Database
Dim rstDoctors As DAO.Recordset
Dim txtDoctorName As Text

On Error GoTo ErrorHandler

txtDocotorName = MsgBox("Add " & NewData & " to the list of doctors?", _
vbQuestion + vbYesNo)

If txtDocotorName = vbYes Then

' Add doctor stored in NewData argument to the Doctors table.
Set dbsIPutMyDatabaseNameHere = CurrentDb
Set rstDoctors = dbsOrders.OpenRecordset("Doctors")
rstDoctors.AddNew
rstDoctors!D... Read more

Answer:auto populate form field

10 more replies
Relevance 89.03%

I have a form with a subform. When I enter the Employee number in the appropriate field, the subform updates with the employee's information. I then want my form (not subform) to automatically populate in another field from the subform that has the individual employee's information. How can this be done. I have racked my brain and I know there has to be a way. OBP, are you out there?
 

Answer:Solved: Automatically populate info into field after subform updates, Access 2007, OB

16 more replies
Relevance 88.74%

I have a form called "New Item Form" that has a three combo boxes- Size, Price and Upcharge. Price is determined by the Size. I have a query for Size and Price (SizePrice). How do I get my form for when Size combo box is selected, it auto fills in the Price combo box? My dB is attached.
I'm kind of new to access so the most clear and direct directions (like step by step) would be greatly appreciated. Thank you in advance! ​
 

Answer:How to auto populate a field after combo box selection

I have answered this and your other posts on your original thread.
 

1 more replies
Relevance 85.84%

Does anyone know the best way to copy records from one field in a table to another field in the same table? There are approx. 40,000 records that need to be copied.
 

Answer:Solved: Access Table: Copy Data from One Field To Another

16 more replies
Relevance 85.84%

Hi,

Over a month ago, I posted this topic here:

http://forums.techguy.org/business-applications/954178-default-value-table-field-access.html

However I never had the time to try out the suggestion. I've finally gotten a holiday though, and so gave it a go. Here's the code I'm using in the After Update Procedure, as advised by OBP:

currentyear = Year([DateofEntry])
newyear = currentyear + Me.[PeriodLength]
Me.[ScheduledDateofRelease] = Left(Me.[DateofEntry], 6) & newyear

It does work, so thank you for that

However, it does produce some odd errors. First of all, it only updates the "ScheduledDateofRelease" after I have clicked "Save" on the form, and once I try to close the form access gives me an error saying that:

"You cannot save this record at this time". In the same dialogue box it says "If you close this object now, the data changes you have made will be lost. Do you want to close the object anyway?"

After clicking yes on that box, and navigating to the table which is the source for this form, I find that the new record has indeed been added, contrary to what the error message was saying.

Another odd thing that occurs is that once I enter a new record into the form and click save, I can't go backwards or forwards through records on the form and am instead presented with the error stating "You can't go to the specified record."

Any help appreciated
Slikbaz.

edit: I've just rea... Read more

Answer:Solved: Default Value for table field in Access 2007

16 more replies
Relevance 85.84%

Hi! This is probably a pretty basic question, but I can't find an answer anywhere here or on the net. How do I add new fields, using VBA, to an existing table in Access? Without destroying thd data? My process: I initially import, using VBA, an Excel spreadsheet into a new table (this table is replaced each month by a new spreadsheet.) To facilitate my analysis of the data contained in that table, I need to add several fields to that existing table. Those fields will be used to store the results of several automated processes using that data. Because the table is created from the spreadsheet provided by another department each month, I need to add the new fields each time I import the spreadsheet. I am trying to do everything in VB because the final user needs to perform all functions by merely pushing buttons on a "Switchboard" My version is MSAccess2003. Thanks for your help!
 

Answer:Solved: Adding a new Field to an existing table in Access using VB

12 more replies
Relevance 85.84%

Okay I hope this will be easy.

I have a form that links to a Table. We input information into this form it then dumps the information into the table. Lets call this table Table 1. We also have a second table that has all of our facilities information (I.E. Site name, location, phone number, and whatnot). Lets call this table Table 2.

Now on Table 1 there is a field that has a listbox of the Site Names you click on it get the drop down and pick the location you want. What it DOESN'T do is the following.

I have a phone number field in Table 2 that I want to auto-populate the phone number field of Table 1 depending on the site picked in the listbox where this listbox reffences the same table 2 I want the phone number from.

EG Table 2 has Site A in the first column and hone number A in the second column, Site B phone number B, Site C and phone number C and so fourth. I want to pick on Table 1 the Site A and have Phone number A auto populate from Table 2's Phone number A and so fourth.

Can this be done fairly easily?
 

Answer:Solved: Access DB -Autopopulate a field from a secondary table.

15 more replies
Relevance 85.84%

I've been working on a database to save subscriber & ticket information for my community non-profit theater group. After lots of trial and a ton or error, I like what I have, but determined that I need to change the way I store some of my data. This required the addition of a text field which has a list of 3 lookup values. I added this to the table in design view--no problem.

The problem is in the forms I've already generated. I went to design view for my form and put in a combo box for this new field. The wizard asks me what I want to do with the values: remember them for later or enter them into a field. I would like them entered into my new field. That's what this combo box is for. However, to enter them into a field, the wizard lets me select only from a drop down list of the fields available in that table, and it doesn't include my newly added field. I imagine that this is because the form itself was created before I added this field. I could recreate the whole form, but I spent a long time designing it, and all I want is to add the one new field. If push comes to shove, I'll start from scratch and do the whole form over again, but I can't believe there is not a way to do this.

If it's of any consequence, this form is actually a subform, but I'm attempting all the modifications to the subform on it's own, so I don't imagine that's got anything to do with this prpoblem.

If someone could please educate me I'... Read more

Answer:Solved: Access--new field in table & problem with form

8 more replies
Relevance 85.26%

Hi. Here is the situation.

I have two tables (Field_a & Table A and field_b & Table b) and I wanna set up validation rule for field_b (Table b).

Rules needed for field_b:
1. If field_a (Table A) = 0, field_b (Table b) = 0.
2. If field_a (Table A) = 1, field_b (Table b) > 0.

Thanks a lot for the help!
 

More replies
Relevance 85.26%

Good Day All,

I have an access database, with an "employees" table. This table contains employee IDs, first names and last names. I would like to use the form (screenshot attached below) to enter an employee id, and query the above table, using the result to populate a label (ie set the caption property). Thanks in advance to anyone who can offer any advice on how to do this.

Regards,

Milty
 

Answer:Use Access Query To Populate Field Value

man, I haven't used access in years.. there should be an onTextChanged event for the textbox in red.. use this event to update the label in blue for each char entered in the text box.
 

12 more replies
Relevance 84.97%

I need to add an field to an index in a table using VB but I keep getting getting

Run TIme Error 3376 Cannot Append. An object with that name already exists in the collection.

Below is an example to try. What I ultimately want to do is add a field to an existing index but in this example I simply try to create anew index and add fields to it.

In this example, I create a table named "t New Test" then try to create an index that would be appended into the TableDef Indexes collection for that table (if I could get as far as adding Fields into the new Index in question.
Code:
Sub TryCreateTableWithIndex()

Dim CDB As DAO.Database
Dim TDFS As DAO.TableDefs
Dim TD As New DAO.TableDef
Dim F As DAO.Field
Dim NewIndex As DAO.Index

Const NewTableName As String = "t New Test"

Set CDB = CurrentDb()
Set TDFS = CDB.TableDefs

'Check to see if there's an existing table with the name we want...
'If already exists then quit
For Each TD In TDFS
If TD.Name = NewTableName Then
Beep
Call MsgBox("Cannot proceed.. a table named " & NewTableName & " already exists", vbOKOnly + vbCritical, "Table Already Exists")
Set TD = Nothing
Set TDFS = Nothing
Set CDB = Nothing
Exit Sub
End If
Next TD

TD.Name = "t New Test"
Set F = TD.CreateField("R... Read more

Answer:Solved: MS Access having trouble adding a field to an table index in VB

This appears to be a case where you have to know the "magic incantation" to make things work.

I was under the mistaken impression that you could just get a field definition from any convenient source (like the TableDef.Fields Collection) and append it to the Index.Fields Collection.

Rather, you MUST apparently use the Index.CreateField method on the Index object
and then append the resulting field to the collection.

In the example above, replace
Code:
'''''''''''''''''''''''''''''''
'This is where the code fails...
'says there's already an object with that name in the collection
NewIndex.Fields.Append F
with
Code:
'''''''''''''''''''''''''''''''
'This work nicely now
Set F = NewIndex.CreateField("Rec No", dbLong)
NewIndex.Fields.Append F
I don't know what havoc might be wreaked if the field definition appended to the Index.Fields collection is different from the field definition (of the same name) existing in the TableDef.Fields Collection.
 

1 more replies
Relevance 84.1%

Now that I have the rate information pulling from my other table, the Rate Total won't calculate unless I overtype the rate field with a new entry. What I would like to do is just press enter to confirm the field and move past it and have the total automatically recalculate.

I think that this would be an event procedure on Enter but I don't know what VBA code to use to make it happen?

Can you please tell me if this is correct and what code will make this recalculate?
 

Answer:Solved: Access 2007, Recalculate field after data is pulled from table. OBP??

8 more replies
Relevance 83.81%

I have 2 tables that are linked by a zipcode field. One one table there is a kinda count field. If the zipcode field on the first table is updated, then I want the count on the second table to increase by one.How can I go about doing this? Thanks for all the help.

Additional info left out: The zipcode field on the count table has a list of zipcodes. When a zipcode is entered on the other table, I want it to match the zipcode in the count table and update the count for that zipcode. Thanks.
 

Answer:Update one field in a table based on another field in another table.

Not quite sure what you are trying to do, but is linking the zip code fields a good idea? Are you tring to find the number of times that a particular zip code is entered?
 

2 more replies
Relevance 81.78%

As a part of my data base I want to issue receipts for a charitable income tax donation.

I want the field to increment by 1.

I have it set to show the format year in the field ie. 2011000 but what I want it to do is assign the number consecutively by the year.

So that when the year changes... 2011 to 2012 it changes the number to 2012xxxx amd increases the number by 1.

the final 4 digits should start over from 0 each January 1.

Attached in 2007 format and 2003 conversion for convenience.
 

Answer:Solved: Access 2007 Auto incremente a field

15 more replies
Relevance 79.46%

Hello,
Firstly, let me apologise, I'm a complete Access noob & am trying to teach myself as I've got no one to help me in person.

Ok,
I'm trying to build a Time Sheet database that should record daily duties via an entry form:
"frm_Work_Hours"

This form updates an underlying table:
"Work_Hours"

The date field of the "Work_Hours" table is:
"Date_Worked"

However, workers get paid fortnightly and all their entitlements, allowances & overtime are calculated on these fortnightly periods. To be more specific, the workers are paid for a certain amount of overtime for each fortnight upfront & when they exceed that, then they are entitled to all excess time back in TIME OFF. So it's pretty important that each day's duties are assigned to the correct fortnight period so that their overtime & so-forth are calculated correctly.

Each fortnightly period is numbered according to year. For example, each year there are 26 fortnightly periods, so as an example today (3rd October 2013) falls in period 2013-19 (23rd September 2013 to 6th October 2013).

I've created a table called:
"tbl_Pay_Period"

Which has the following Fields:
"ID" (Auto-number)
"Period_Start" (date - the first day of each new fortnight period)
"Period_Number" (number - eg: 2013-19)

Back in the "Work_Hours" table, I inserted a new (Number) FIELD called:
"Pay_Period"

and set ... Read more

Answer:Solved: MS Access - How to get a field auto-update based on date entered in a form

9 more replies
Relevance 79.17%

Ok, my problem is...i have a table with 2 field diferent name

Example
ID 1
unit_price 238.56
unit_pricelast ......

It is possible to update "unit_pricelast" with value of "unit_price" but using same ID?

Result:
ID 1
unit_price 238.56 (later i must change value of this field cuz i have items with last month price and price with actual)
unit_pricelast 238.56

ty for fast respond
Goran
 

More replies
Relevance 78.3%

I'm fairly new to access and have created a database which amongst other things, keeps track of the current status of drawings.
I have a table called "Drawing Register" which contains the information on a list of drawings including drawing number, title, revision and status.

I then have another table called "Review"
In this table I can obtain the title of the drawing from the drawing register, then i manually enter comments on the particular drawing and also it's status (Status being either A, B or C)

I want the "Drawing Register" table to be updated with the status of the drawing (when it is entered into the "Review" table.

The reason for this is I want to print a report from "Drawing Register" showing the current status of each drawing.

Now is the bit I cannot work out.

If I review a drawing which for argument's sake is called "100", I enter the current status (eg Status A)
The "Drawing Register" table updates the "Review Field" to "A"
At a later date, I may want to review drawing "100" again (but create a new record as I don't want to overwrite the previous review record), so if I add a new record in the "Review" table and add new comments for drawing "100", and then I make the status "B", the "Drawing Register" updated the "Review Field" to "B" which is correct, but it also updated the prev... Read more

More replies
Relevance 78.3%

Hi-

I would like to add a field to the end of my table called Timestamp. I want this field to be populated with the current time if a record is updated. I know I can set the default to =Now(), but that only sets the time and date the record is created. If I go in an update this record, that time or date does not change. Is there a way to inset a time and date that would automatically change? If not, is there an easier way then to just type in the time and date manually? Any suggestions would be appreciated. Thanks!

Ian
 

Answer:Adding a timestamp field in a table in Access

Have you tried =Date() ? That is what is used in the contact management database that comes with Access 2000.
 

1 more replies
Relevance 78.3%

I'm fairly new to access and have created a database which amongst other things, keeps track of the current status of drawings.

I have a table called "Drawing Register" which contains the information on a list of drawings including drawing number, title, revision and status.
I then have another table called "Review"

In this table I can obtain the title of the drawing from the drawing register, then i manually enter comments on the particular drawing and also it's status (Status being either A, B or C)

I want the "Drawing Register" table to be updated with the status of the drawing (when it is entered into the "Review" table.

The reason for this is I want to print a report from "Drawing Register" showing the current status of each drawing.

Now is the bit I cannot work out.

If I review a drawing which for argument's sake is called "100", I enter the current status (eg Status A)
The "Drawing Register" table updates the "Review Field" to "A"
At a later date, I may want to review drawing "100" again (but create a new record as I don't want to overwrite the previous review record), so if I add a new record in the "Review" table and add new comments for drawing "100", and then I make the status "B", the "Drawing Register" updated the "Review Field" to "B" which is correct, but it also updated the pre... Read more

Answer:Access 2003 - Update a field in another table (one way)

Hi Phil, welcome to TSG Forums!

If I got the picture right then your approach needs to be altered a little bit.
1) The Status field should be removed from Drawing Register table, beacuse the status is, actually, not a property of the drawing, but that of the review. Additionally, placing the same data in two or more different tables/fields is breaking one of the most basic rules of databases.
2) A query should be created, which contains all the necessary fields from Drawing Register table, and also the newest Status from the Review table. The tables can be joined by the name or ID of the drawing, and the query can be used as source for report printing.

Jimmy
 

2 more replies
Relevance 78.3%

I am using a query in Access to clean up some unneeded text from a table, then create a new table with the cleaned up text. In addition, I need to add a column to this new table that is boolean format defaulting to a "no" value. I have successfully created this query except for the new boolean column. I can get the new column to default to a value, either no or 0, but it is in text format. If anyone knows how to do this I would appreciate the help.
 

Answer:Access-Adding a field to a table with a query

This query will do it:

ALTER TABLE TableName ADD COLUMN NewColName bit
 

3 more replies
Relevance 78.3%

Hi,

I have a table which is quite large, and is updated every week, so far no issues, but this week I made a mistake when entering the week number and had to delete all the entries from the database , this was a endless task as filtering the week took some good time... question is:

Is there any way that I can "update" a field on the table with the new field, all of them are teh same value, in this particular case is a week number.

Appreciate the help.

Thanks in advance,
 

Answer:Access 2010 - Update field in a table

7 more replies
Relevance 78.3%

I'm relatively new to access, only been working with it on and off for the past few months or so and have run into a little problem now.

Basically, a date is entered in a field on a table called "Date of Entry", and another value is entered in a different field called "Serving Period". Now I have another field called "Expiry Date", and basically it's the value (date) of "Serving Period" added to the date in "Enter Date". I suppose that it would possibly have something to do with the Default Value of the "Expiry Date", but that's where I'm confused. Of course, I need not make it automatic and let the user do the math himself, but I'd rather it was done this way if possible.

I'm using Access 2007 on Windows 7.

Any help appreciated.
 

Answer:Default Value for table field in Access 2007

9 more replies
Relevance 77.43%

I have a control in a form that calculates the Gross Monthly Income based on amount entered in Annual Income (=IIf([AnnIncome]>0,[AnnIncome]/12,0)). Can the results of this expression be used to update the Gross Monthly Income field in the underlying table?

Thanks!
 

Answer:Access-Update field in table from expression in form?

10 more replies
Relevance 77.43%

I am currently using Microsoft Access 2002. I wanted to know if there was a way for me to append an entire column from a table into a single memo field.

I am trying to make a distribution list memo field in an access table. I wanted to grab an "email address" column to upload into this memo field. I could reference the memo field later on when I send emails from Access.

Is there any way to do this?
 

Answer:Microsoft Access - Appending Table To Memo Field

First, you don't really give enough info: is this a one time append or is this for continuing functionality in a form?

If it is the latter, what you would probably need to to is create a list box that populates all the email addresses in your database. Then, based on which items in the box you chose, you could loop through, using vba, and create a string value. Once you have the string with the email addresses, you would commit that to your memo field.

Hope this helps,

MBN
 

2 more replies
Relevance 77.43%

This seems like it would be a common task in Access but I can't find any reference to it.

I have a combo box with three choices, Kamazaoo, Grand Rapids, and Traverse City. The field name is location. The location is entered only once but there is no limit to the amount of records that can be added. When one record is finished Access defaults to the previous location and starts a new record.

I know how to do a make table query but can I do a make table query based on the fields value? For instance if the entry person is finished entering data for Kalamazoo a button could be pushed that would make a Kalamazoo table.

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

Answer:Make table based on form field in Access

16 more replies
Relevance 77.43%

Hi There

To make a long story short say I have a table called Table1 with only 3 currency fields called Deposit1, Deposit2, Deposit3 and TotalDeposit. I then create an input form based on Table1. On the form, In design view, I use all 4 fields. On the TotalDeposit field on the form, I do a calculated field as follows:

[Deposit1]+[Deposit2]+Deposit3]

I then populate the 3 fields with currency - $100, $200 and $300

On the form the calculated field shows a total of $600. However, when I go back to the table, the TotalDeposit field has a zero? How come? How do I get the calculated field on the form to appear on the table??

Mario
 

Answer:Access problem in calculated field on form to appear in Table

Hi Mario

How exactly have you set your TotalDeposit field to be populated with the sum of the other three fields? That is - where have you typed [Deposit1]+[Deposit2]+[Deposit3]?

Deej
 

2 more replies
Relevance 77.43%

I have created a database in Access 2007 with 4 tables, Customers, Products, Orders, and Order Details (the specific items for each order). I know you don't want to dupliate data and "calculated fields belong in reports and queries" but we change the prices frequently and I need the UnitPrice from the Product Database to "update" to the Order Details Table on the day the order is input.

I have a Order Input Form with 2 sub-forms and my relationships are set one-to-many on primary keys. All other queries and forms work great. I have tried looking at the Northwinds database to determine how they do an "AfterUpdate" event procedure but I haven't used VBA since Access 97 and am not able to convert it for use with my form.

Help is appreciated!

 

Answer:Access 2007 Update Table Field from Form

Cosla, have a look at the database that I posted here
http://forums.techguy.org/business-applications/931652-vba-ms-access-2007-a.html
at post #5.
 

3 more replies
Relevance 75.69%

Hello,

I have been struggling with this for a couple of days.

Here's the scenario: I have 2 sets of data, one shows repeat calls for a list of customers, the other shows trouble reports for the same set of customers. I want to count how many times each of these customers called within 30 days of the original trouble report.

I have tried using a nested count Iif expression but I don't have a lot of experience with expressions and apparently am using it incorrectly. What I have tried is something like this:

=Count(IIf([Contact Date/Time],+30,1))

Any suggestions or ideas are welcome at this point. Thank you in advance!

Thanks,

sdl19
 

Answer:Access 2013 - Count only if date falls within 30 days of field from different table

I would create a 'groupby' query using the DateAdd function for criteria. If this is a report you should be able to link this query to the report query by acct number or something.
SQL looks like this example
SELECT Table1.Name2, Count(Table1.ID) AS NameCount
FROM Table1
WHERE (((Table1.Date1)>DateAdd('d',-30,Date())))
GROUP BY Table1.Name2;
 

1 more replies
Relevance 75.69%

I have a form that I'm using to enter orders for inventory. I just ordered 2 phones I'd like to have listed as 2 separate records (they will be physically tagged individually, so they need to be separated in the table).

I'd like to have a Quantity field that when I type a number and save the record from the form, the number in the Quantity field adds that number of identical records (aside from the Autonumber field for the primary key).

Is this possible? If not, how should I go about it?

We have larger orders, sometimes 10-20 items, all with the same everything in the record except for the physical tag number that will be applied to each item when it comes in.

Thanks in advance.
 

Answer:Access 2010: Form quantity field that adds records to table

If the form is for entering the actual order details then you shouldn't have a record in the order table for each item in the quantity field.
What you could do is is to add a record for each item in the quantity field in the Inventory table using the Item's ID to identify it.
 

1 more replies
Relevance 75.69%

I have a form (parent form) that has more than 1 pages and on 1 page I put a sub-form with text boxes and combo boxes linked to a table say Table 2.
In this Table 2 it contains historical data of a particular field, say previous employers and the current employer and the data on each employer of a person, so there's a field: a flag to mark that a record is the current one and the other records of the same person are not flagged.

On the parent form, there's a list box displaying content of say Table 1, say list of people.
Whenever a row is selected in this list box, the text boxes and combo boxes on the sub-form should display this person's current data (a record with the current flag checked).
So I linked the list box to the sub-form by the person ID in both tables, by populating the child and parent fields on the forms.

What I'm confused about is the first text boxes display the current dummy data, but then the combo boxes are empty.
It's dummy data cos for now in Table 2, there's only 1 record for a person.
I haven't put the criteria to display data that has the current flag checked, cos there's only 1 record per person now.

I linked each combo box to the relevant field of Table 2 on the "control source".
There's nothing in the "row source" of the combo box, even though later on there should be.

Any idea what went wrong?

Thanks in advance.
 

Answer:Problem displaying value of a table field in a combo box in a subform in Access 2003

9 more replies
Relevance 75.4%

I have a nice little database that I set up to track donations of items to our organization. The primary table tracks the donor information: name, address, etc. (main form) and I have created a related table that tracks the date of donation and items donated utilizing a sub-form. Thanks to the fine help I received here I was able to relate these tables using a “Link_ID” field and it has worked nicely. (One donor could have multiple donations.)

Now, I’d like to enhance things a little bit. When I enter donor information into the main form and then go to the sub-form for the donation information, I’d like the business_name field to autofill as I start to type. Now, I know this can be done because most commercial databases have this function. Quicken is one that comes to mind. I know it has to pull this information from somewhere, I just need to know how to get it to work!

I have been unsuccessful finding any pertinent information on an “autofill” field in some sources. (I really don’t want a drop-down list box because this could potentially get too large.)

TIA.
 

Answer:Auto complete or Auto Fill field in Access?

Yes, well, unfortunately, in Access, the drop-down list is the only type of control that uses auto-fill, unless you want to use a text box and code (there's an example of that in the developer's handbook, but it's complicated).

No matter how big your list is, if you sort the underlying query alphabetically on company name, it will still autofill, and you can even build a not-in-list response to add new customers...

Make sure the auto-expand property of the drop-down (or combo) control is set to Yes, and you'll see that you never even have to drop down the list...
 

2 more replies
Relevance 105.78%

Is there an easy way to input a SS# and have the cursor move to the next field without me hitting the tab or enter key? I have looked everywhere.
 

More replies
Relevance 104.96%

I am working on a database that stores genus, species, and variety I want to sort the data with genus, then species, then variety.

Now the tricky part. Sometimes there is no species. When this is the case, I want the database to sort that record off of variety.

I have though of a way to do this, but need help with the code.

If I make a species2 field, I could create an expression that would look at the species field. If the species field is empty, it could copy the data from variety. If it is not empty, then copy the data in the species field.

Once the species2 field has the updated data, I could then do a sort

genus, species2, variety

The report would have genus, species and variety showing and species2 would be checked off to not show in the report.

I am using Microsoft Access 2000.

Any help would greatly be appreciated.
 

Answer:Access - if field is blank, copy field A, if not copy field B

You should not actually need another field to do this, just another column in your report's Query. Something like this as a Field header :-

dummySpecies: iif(isnull([species]), [genus], [species])

this should provide a column with the species in if it there and the genus if it is not, which you can use in place of your actual species field in the report for sorting.
 

1 more replies
Relevance 103.73%

I'm trying to find out why Access 2000 will auto fill my data entries. I've noticed that when I put a number in a field that the next record will automatically insert the next number in that field. For example, in my Order column, in datasheet view, I enter a 1, the next record in the the Order column will automatically fill with a 2 and so on. Or the pattern will repeat - if I enter a 1 in the Order field, then a 3 in the next Order field, the next Order field will fill with a 5. What is going on? and how do I stop this?
 

Answer:Access 2000 does auto-fill on field data

momish, I can't find how to turn it off either.
If you are using a form in datasheet view then you can convert it to "Continuous" forms mode and that will overcome that problem for you without losing the functionality of the datasheet ease of entry.
 

2 more replies
Relevance 102.09%

I've upgraded from Excel 2000 to 2007 and cannot get the pivot table field list to show. I activate the pivot table and switch it from hide/show either by right clicking or from the toolbar but still no joy. I''ve looked all over the spreadsheet to see if it hidden anywhere but cannot find it. Any suggestions? If it is hidden somewhere can I move it via vba code?
 

Answer:Solved: Excel 2007 Pivot Table Field List Missing

16 more replies
Relevance 101.68%

I am writing a database in Access 2003 using Windows XP Pro.

I have two separate tables - one that contains identifiers for people (last, first, middle, dob, ssn, etc.) another that contains information for companies (name, state, date, number for incorp, etc.)

In various different portions of the database, these tables are used individually. For instance, the two tables are linked (through a separate table ) to relate people to companies that they are associated with and identify their roles.

Another table in the database contains certain information about civil and criminal lawsuits. One of the fields in that table - "defendants" - needs to be able to store a link to EITHER the person table OR the company table (defendants can be either people or companies)

I created a joined table that contains both the people and the companies in one field. But, although this will allow me to ease data entry, it does not actually create a link back to the source table - using the primary key from both tables in the join creates a field where there are two 1's, 2's, etc. and no way to uniquely identify the information.

I created a table that contains as field one a unique identifier, as field two the people table id, as field three the company table id. I then created a query off that table with a concatenated field of first, middle, last, company (so that it would show either the name or the company name.) The query does not work because, by design, either t... Read more

Answer:Combining two data sources to populate one field?

Danerys, can you please post or email me a zipped copy of the database.
It can either have no data a a little dummy data for me to work with.
I will take a look at it for you.
 

1 more replies
Relevance 100.86%

I have started creating an Access 2007 database for users to audit part number setup information entered in separate ERP system, but need advice on best design to allow for auto-populating some of the values and user-entry for other values. Need to record part info audited, auditor name, audit date, pass or fail, add'l concerns or comments.

Scope of Auditing
Performed Monthly and Quarterly
Multiple clients with unique criteria for some values
Hundreds to thousands of parts audited monthly
Tens of thousands of parts audited quarterly
Multiple useres performing audits
Criteria for two of the values audited can change
weekly (or in a some cases more often)

Manual auditing due to frequency of changes to criteria on the two values, but considering ways to automate the auditing in the future.

Working on Quarterly Auditing functionality first because that will provide most benefit.

Database has a table "Quarterly Audit" with fields populated from query that links to tables in ERP system and has criteria to pull the correct parts and part info. It also has following fields to record auditing info - "Performed By", "Date", "Pass or Fail", and "Addl Concern(s) or Comments".

I have a login form in place and am setting a global variable "gbl_User" = username. Value set during login to the database.

I have a form "frm_QuarterlyAudit" with a subform "frm_QuarterlyAudit_sub" bound to the "Quaterl... Read more

Answer:Solved: Easiest Way To Auto-populate And User Enter Info Into Table?

9 more replies
Relevance 100.04%

Hi there,
I am not that cluey with access vba programming and I need some help please.
I have a Table called (Parts) with columns (PartName), (PartNumber), (PartCost), (Quantity)...
I have (Parts) table linked to (Repairs) table in one to many relationship.
I have a Form linked to the table (Repairs) with different fields to enter different repair details.
Among these fields is: combo box (PartsNameCombo) where you can select the Part Name from values listed in (PartName) column in table (Parts), and I have used the following codes to populate the selected part's cost accordingly:

Private Sub PartNameCombo_Change()
'Auto populate Part cost based on Part Name
Me.PartCost.Value = Me.PartNameCombo.Column(2)
End Sub

I have repeated the above combo box three times (in case different parts used in one repair).

My Question: I would like to add code to automatically subtract (1) from part's Quantity in (Parts) table whenever a particular (PartName) is selected in the combo box.
so for example, if in a repair, I selected: (front case) in the first combo box and (display screen) in second combo box, I want the cases quantity to drop by one and also screens quantity to drop by 1?
Any one can please help?
 

Answer:Updating table value when different table form field updated

16 more replies
Relevance 100.04%

Hi there,
I am not that cluey with access vba programming and I need some help please.
I have a Table called (Parts) with columns (PartName), (PartNumber), (PartCost), (Quantity)...
I have (Parts) table linked to (Repairs) table in one to many relationship.
I have a Form linked to the table (Repairs) with different fields to enter different repair details.
Among these fields is: combo box (PartsNameCombo) where you can select the Part Name from values listed in (PartName) column in table (Parts), and I have used the following codes to populate the selected part's cost accordingly:

Private Sub PartNameCombo_Change()
'Auto populate Part cost based on Part Name
Me.PartCost.Value = Me.PartNameCombo.Column(2)
End Sub

I have repeated the above combo box three times (in case different parts used in one repair).

My Question: I would like to add code to automatically subtract (1) from part's Quantity in (Parts) table whenever a particular (PartName) is selected in the combo box.
so for example, if in a repair, I selected: (front case) in the first combo box and (display screen) in second combo box, I want the cases quantity to drop by one and also screens quantity to drop by 1?
Any one can please help?
 

Answer:Updating table value when different table form field updated

At least two ways to do this but I wouldn't make it automatic. What if the wrong Part name was inadvertently selected? I would ad a button to the form "Take one from Inventory" and execute an update query that basically does Quantity= Quantity-1 . This query would need to link to PartNumber on the active form.

The other way would be to write the SQL to do the update in VBA and run the EXEC SQL command under the new button.
 

2 more replies
Relevance 97.99%

Basically I have a situation where users are creating data tables in Microsoft Word using the creating table functionality, and are then resquesting that the data will be imported into a single Microsoft Database field.

Please could someone show me the best way of doing this!!!!!!

Cheers !!!
 

Answer:Importing Table Data Into Multiple Access Field Data?

Do you mean they're creating, say, a 3-row by 3-column table and then they want to import everything in that one table into a single field? a single record?

Out of curiosity, what happens if you copy and paste, perhaps with an intermediate step through Excel?

Also, is there a reason why your users cannot work directly in Access?

More details, please.
 

3 more replies
Relevance 97.17%

Here is what I'm trying to do.

For lists Equipment in drop down box.
Whatever equipment that is select, the equipment type field needs to be updated from a table.

Is there a way to get a value from SQL statement?

SQL = "SELECT [Equipment Type] FROM OrderDetails Where " _
& " Equipment = '" & Me.Equipment & "'"
[Forms]![OrderDetails]![Equipment Type] = SQL
 

Answer:Help with access form (insert table value into form field)

Mhouser, if you are trying to "display" a value related to the Combo selection you can have thta value as an extra column in the combo and refer to it with simple VBA.
You should not populate a Field's actual value with that from the combo as that is duplication.
Can you tell me which one you are trying to do?
 

3 more replies
Relevance 95.12%

I use due dates on my Outlook tasks. When I customize my Tasks list (in the Calendar view) to group by due date, I just get too many groupings (one for each due date!). I would like to know if I can create a custom field called 'Due Month' and/or 'Due Week', extracted from the 'Due Date', so I can view my tasks by what is due during a given week or month. If the answer is yes, any suggestions on the technique I would use would be greatly appreciated.
Thanks in advance,
Mac
 

More replies
Relevance 95.12%

Using Access 2003-

I need to use an existing table field value in an If statement- I have found only many different ways to get error messages.

The user selected the table name "Module" and is just a text name.

I want to look up the value of the field "Valid" in the "Modules" table.

If the value is "Yes" [straight text field] then I want to open a form and have that module number be already loaded into the "Module Number" field.

If the value is "No" then I want a pop-up form that tells the user "That number does not presently exist. Would you like to create a new Module now?" with two buttons- one that says "Yes" and opens the new module form and the other to just say "Cancel".

Thanks for your help!
 

More replies
Relevance 95.12%

I have a database where I have created a form with 5 places to chose types of errors on an application. Those errors are associated with a Grouping. For example: In the table called Summary of Errors I have an error that reads " Agent Calculated New Rates" in the column beside it the Grouping would be "Premium Differences". Within the form, if I selected the "Agent Calculated New Rates" error I would want the grouping to auto populate in the table. Could someone please explain how to do that. I am sure its something simple.
 

Answer:Autopopilating a field in a table

6 more replies
Relevance 94.3%

I am setting up kiosks that once in the field will be locked down and not accessible to anyone in the field nor do we want anyone else accessing them. However, I need the ability to remote into the system if there is an issue.

Is there anyway to set up the machine so that I can do this without someone being in the field at the machine?

Brett.

Answer:Remote connect to a system in the field w/o user in field

Teamviewer.

3 more replies
Relevance 94.3%

I support a software program that has a proprietary menu item when you right-click a calculated field. It has been this way for 10-15years and has worked in WinXP through Win10 with never an issue. This past week I had 3 different customers tell me that when they right-click one of these fields to access what is normally the Cut/Copy/Paste/ menu that also includes our menu item, they are now getting the Windows default cut/copy/paste/Right to Left Reading order/Show Unicode control characters/ etc menu. The only thing I can think of that they all have in common besides these are all Window 10 64bit pc's, is that they all have one update to Adobe Acrobat (17.012.20098).
I was able to resolve for a moment on two of these workstations by having the user go into Language/Advanced settings and actually selected "English (Unites States) from the "Override for Windows display language" even though that settings is implied by the "recommended". But after the pc was turned off over night and back to it the next day, the problem is back and that setting is still selected. All language and regional settings on all the pc's are defaulted to English/Unites States. I do not know what other avenue to go in. Could something hae been switched on or off in Windows (by this Adobe update perhaps?) that would make the context text menu switch to Windows text default right-click menu?

Any help is appreciated!
 

More replies
Relevance 94.3%

I need a date field changed into a text field of YYYYMMDD so for example the date of birth field is 3/17/1953 and I need that converted to 19530317 in the Excel spreadsheet. Thanks.

Answer:I need a date field changed into a text field of YYYYMMDD

under the format options.

5 more replies
Relevance 94.3%

I'm doing a quick-and-(very)dirty design as a proof of concept. During this process I fat-fingered the word Description, as Ddescription. No problem, I know what it means, right. But it annoyed me, so I changed the underlying table (where the error occurred), deleted the control from the form, and added the (now correctly spelled) control.

However, every time I try and ruin the form it wants a value for the now non-existent field Ddescription. I simpy click-through, and the form runs fine. When I look at the available fields in the form's View query's Field List, it still shows Ddescription, and i seems to think it's coming from the underlying table.

Any ideas on how I can remove this phantom field? I can't find anything called "The_View_for_form_FooBar" anywhere, but I know it must exist somewhere...

Tony
 

More replies
Relevance 94.3%

I have an access 2007 database that has fields for several addresses. I would like to have access skip subsequent address fields if the previous field is blank. I have done this before but for the life of me cannot remember how it was done. Thanks.
 

More replies
Relevance 94.3%

I attached my database what i would like to do is create a form when an end user will enter a corresponding user ID from that I wish to populate textbox fields with Agent Name, Supervisor, Location, Dept *all seperate text boxes of course* I have attached the database if someone could help me do this it would be great. Let me know if you need any further details to help get me through this.
 

Answer:update field A in form based on entry in field b

Welcome to the forum, before answering you question can I suggest some improvements to your database.
Currently you do not have any "Key", "Indexed" fields in your tables and there are no relationships set up between the tables.
Also do you really need the Archive Table?
The data looks like it has been exported in from a spreadsheet, relational databases work quite differently to spreadsheets, so to make the most of their advantages you need to correctly relate your Tables.
Your UserID Roster also has some disconnect between the Field Names and the actual data in them, particularly the Name and Type feilds.
In the Supervisor Table the Birthdate does not seem to have Translated correctly.
 

2 more replies
Relevance 94.3%

I have 2 tables and need to copy all the values from one field of tblB to an existing field in tblA.

I am using the RunSQL command from within an Access Macro and have inserted this SQL Statement.

UPDATE tblA SET fieldA = fieldB from tblB

This syntax does not seem to be correct since I am getting error message:
"Syntax error (missing operator) in query expression 'fieldB from tblB'."

Can you please help.

THANKS
 

Answer:SQL statement to copy field from one table to another

try this:
select * into tblB from tblA

I hope these helps.
good luck.
 

2 more replies
Relevance 94.3%

I have a column for Count of Missed rooms, and a count of Total rooms. I would like to use a calculated field to create a % of Missed and Successful.
Every time to try to create a calculated field, I get #DIV/0! and 0
What am I missing here? I've even taken the raw data and moved it to a new workbook to try and re create it... same issue
I'm attaching a print screen
 

Answer:Pivot table Calculated Field not doing its job

the fields with Red and Blue are added in... they are not part of the pivot table. In fact, they have the formulas that I want... Only, I want them as part of the pivot table instead of formulas just sitting there
 

2 more replies
Relevance 94.3%

I have a table linked to a form which is "=now()" and I do not the end user to be able to change this field however I cannot apply user/group permissions

Any Ideas????
Please
STUART
 

Answer:Solved: Access NO editing in a certain field

Please can somebody help me
 

3 more replies
Relevance 94.3%

Im having trouble trying to get my search box to just display the data i want!
I have a table called TblInfoQubeRef Which has 2 columns called SiteName & QubeRef. (Populated to use a combo)
I have another table which i have many fields - to hold info about telephone lines for the SiteName. I May have many lines for the one property.

I created a QryDataMainLineSearch with certain fileds in of data i want displayed.

My end result is, i want a form with a combo box displaying the site names - when i select the SiteName it populates the data below of all phone line for the SiteName.
Can any body help - OBP?
 

Answer:Solved: Access Search Field

11 more replies
Relevance 94.3%

My calculated field populates a combo box. From it's selction, how can I display the ID of the selected value in a textbox? Thanks.
 

Answer:Solved: Access: ID from calculated field

10 more replies
Relevance 94.3%

I am trying to add an attachment field to an existing table in ms access 2010. When I try to add it I get a popup that states "operation not supported for this type of object". Does anyone know how to make this work? I am not an Access expert by no means.
 

Answer:Solved: MS Access Attachment Field

6 more replies
Relevance 94.3%

Hello,

I researched this issue on the net, but couldn't find any solution that I could actually make work, so I am asking for your advice here..

I have lots of input data that I need to combine and process in Access. The issue is that the input data is mostly in table format, showing data by week, month, quarter and year-to-date,like how it is shown below.
Location Metric wk1 wk2 ....wk52 Jan Feb .... Dec Q1 Q2 Q3 Q4 YTD
The data in this format of course, violates first normal form, but this is the form the data are available in, from multiple data sources..

As stated above, I need to combine data into one table, so I think I need to create a couple for cycles i=1 to 52 (for weeks), j=1 to 12 (for months), etc. to be able to combine the numbers into one big table. Within the cycle, I need to update data for each metric, for each week, month, ...

So, the question is, how can I reference variable values as field names? Based on my research, this is how far I could get, but of course, it doesn't work:

Dim rsOntime, rsData As Recordset
Dim MVar As String

Set rsData = ["Data"] ' Table to combine all data into
Set rsOntime = ["Ontime"] ' One of the input tables
i = 1
j = 1
k = 1

' Loop for metrics
For k = 1 To 24

' Loop for weeks 1 to 52
For i = 1 To 52

If i < 10 Then iText = "0" & i
If i >= 10 Then iText = i

MVar = rsOntime.Fields(iText)
rsData(MVar) = rsOntime!InputData

.RunSQL ("Update Data IN... Read more

Answer:Solved: Access - use variable value as field name

6 more replies
Relevance 94.3%

Long time no see, took a bit of a break... time sure fly by huh?

My Query's Y/N collum result in -1/0 when I run the query. I want it to be Yes/No.
Is there a ... better/quicker way to do this other than using IIF() function in the sql?

I use this field in a combobox on a form, interesting thing is some of the combo's display Yes/No whilst some display it as -1/0

Thx
 

Answer:Solved: Access Query Y/N Field

7 more replies
Relevance 93.07%

I have a macro where I need to append serial number records to a table. I need to repeat this x number of times based on the value in the Field qty_to_ship of Table ProdOrderQtyToMake. I have to generate serial numbers for labels that start with a value of 0001 and end with the number of qty_to_ship value.

For example, if ProdOrderQtyToMake.qty_to_ship = 375. The serial numbers will be 0001-0375. I need to run the append query 375 times. I have the append query in a macro called mcrAppSerialNumbers.

I cannot figure out the correct syntax to enter into the Repeat Expression of my RunMacro action. I've tried so many things with DLookup and DCount and other ways but nothing works. Can someone tell me how to do this? Please?? (and thank you!)
 

More replies
Relevance 93.07%

Dear all,
I don't know how to elaborate my problem. But what i wanted to know is Eg, i got a master sales type table [ ID, orderTYPE , OrderNo] which is related to sale table [id, ordertype, orderno date] in a relationship of 1 to many.
SO, how can i insert 2 fields (ordertype and orderno) into a form in which i would select the ordertype from the Sale Type table by combo box and it will auto-shown the orderno which is in sale type table. Meaning that i would need the sale order no to be like YD (ordertype) 1(orderno) , YB.1 and so forth.The orderno in sale table is duplicated as well as the ordertype.
Hope u help me out.

Thanks a lot.
 

More replies
Relevance 93.07%

Hi There

Does anyone know how to convert a merge field (for multiple letters) into a table.

I have a list of assets and values saved in an excel workbook seperated by a | and want to convert the merge field into a table once all records have been merged. Its impossible to split the cell in excel (truth be told theres more than one list and without knowing how many assets there are in each cell I will end up overwriting data and the excel file is already big enough)

For someone reason it converts the merge field into just one cell of a table.

I tried this on a just plain text (same data) and it works fine.

If anyone can thing of a way to do it hopefully as part of a mailmerge before/after event it would be appreciated.

Thanks

Matt
 

Answer:Word - Converting merge field to table

You need to click on the "Update all Labels" button when using the Label format. I think it is the 4th step into the wizard at the bottom of the taskpane. It is easily missed by many.
 

1 more replies
Relevance 93.07%

The Field List does not appear when a pivot table is created.
Right mouse click shows "Hide Field List" but the field list is not shown.
Even going to Pivot Table Options does not show the field list.
Closing Excel and Rebooting does not clear the problem, even when a new spreadsheet is opened.
 

Answer:Show Field List in Pivot Table

George,

Excel 2003:

Click within your pivot table to get focus. Select View>Toolbars>Pivot Table.
From the toolbar select 'Show Field List"

Stoneboysteve
 

3 more replies
Relevance 93.07%

InvoiceInv DateCompanyNameRefEvent CodeEvent NameEvent DateQty NetVatGrossPaidPay DatePay MethodCardCheque No.

I have an existing worksheet I wish to pivot table to a seperate sheet.

I have these headings in the existing sheet.

I am confused as to what the pivot table fields names should be as I keep getting the macro to stop with pivot table field name is not valid.

Here is the code, can you help please I am not an excel or macro expert so please be aware I may have created some very juvenile errors?

Sub CreatePivotFinal()
'
' CreatePivotFinal Macro
' Macro recorded 7/14/2004 by Erikka
'
Sheets("Summary_by_Payment_Method").Select
Rows("1:1000").Select
Selection.Insert Shift:=xlDown
ActiveCell.FormulaR1C1 = "'Pay Method'"
Range("A16").Select
ActiveCell.FormulaR1C1 = "'Credit Card Type'"
Range("A17").Select
ActiveCell.FormulaR1C1 = "'Event Code'"
Range("A7").Select
ActiveCell.FormulaR1C1 = "'Gross Amount'"
Range("A13").Select
'.Range ("A1"),
'create the pivot table in the sheet named Summary_by_Payment_Method
Sheets("Summary_by_Payment_Method").Select
Cells.Select
Selection.Clear
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:="'Accounts Extract'!R3C1:R1000C18").CreatePivotTable _
TableDestination:="Summary_by_P... Read more

More replies
Relevance 93.07%

Update a time field
I have a Table with 2 fields. The 1st field is Labeled "TimeStopped". The 2nd field is Labeled "TimeDiff". I need to subtract the next record of TimeStopped field from the preceeding record TimeStopped field and put the difference in field 2 TimeDiff so I can know how long each stop was.
Any help in using Vba to do this.
Thanx,
john
 

Answer:Solved: Update a time field with access vba

16 more replies
Relevance 93.07%

On a form, need assistance in fixing a vb script msgbox problem. I have placed the code listed below to verify that the user wants to change an existing value in a field. The code below works fine, UNLESS the user has made other changes on the form prior to changing the checked fields
There are 2 fields ("NoBill" and "Lname") that are checked for changes. But if someone changes a remarks or other field in the form prior to changing "NoBill", and when the msgbox pops up to verify the change, if the user selects "NO", then the other fields that were changed before are also undone.
Q: how to just undo that particular field, not all the changed entries on the form.
Code:
Public Function Conf()
Dim Msg, Style, Title, Response, MyString

Beep
Msg = "You have altered existing data are you sure you wish to do this?"
Style = vbYesNo + vbExclamation + vbDefaultButton1
Title = "Confirm record change"
Response = MsgBox(Msg, Style, Title)
If Response = vbNo Then
Screen.ActiveForm.Undo
Exit Function
DoCmd.RunCommand acCmdSaveRecord

End If
End Function

and then in the Before Update properties
Code:
Private Sub NoBill_BeforeUpdate(Cancel As Integer)
If Not Me.NewRecord And Me.Dirty Then
Call Conf
End If
End Sub

I tried creating two separate public functions ("Conf_NoBill" and "Conf_Lname"), and calling those particular functions based on those fields' Before Update ... Read more

Answer:Solved: Access 2k field change verification

13 more replies
Relevance 93.07%

Hi
Can someone help me to put a dash in the middle of 8 numbers on a number of records in a field in Access.
eg change 12345678 into 1234-5678 (each record in the field contains a unique number)

Thanks
 

Answer:Solved: Access Manipulate data in a field

7 more replies
Relevance 93.07%

Hi,

I have two tables and i want to lookup a value of one from the other.

The first table contains a field called Department.
The other field has Department ID and Description.

I want to display the descriptions in the first tables Department field.

I've tried the lookup wizard data type in design mode, but it just returns the Department ID's instead of the descriptions.

EDIT

problem was my logic. no worries.
Any help appreciated.
Lewis
 

Answer:Solved: Access 2007 Field Lookup

Lewis, it should only show the ID, as that is what it should store. Seeing the Description is what Forms are for, users should not really interact directly with tables.
 

1 more replies
Relevance 93.07%

Hi all,

I need some more help with a form in Access. I used the wizard and created a form with all fields I thought I wanted. Now I want to add another field (item from the field list) from a table in my database. I cannot find a way to update or change the field list of my form once I have created it.

It is the same form I had help with last time (an overtime tracker). I select an employee from the list who has his salary info saved in the employee table. When I select the employee in my form, I want to display his salary in another text box so I can use it for calculations.

On a similar note. I updated the table that my form is based off of and renamed a column. Whenever I open the field list in my form, the same column heading remains even though it exists nowhere else in the database. How do I update the field list?

Any help would be appreciated. If you need more info please let me know, thanks.
Ronyace
 

Answer:Solved: Adding field to Access Form

10 more replies
Relevance 93.07%

Hi,
I am trying to set up a field to increment for each new record. I know about auto increment but the way I have it set up their are two sets of numbers. 1 for the group number and one for the Item # of that group. The group number increments when a button is pushed. The Item number would increment either when a button is pushed or the user tabs through all the fields. Example 1-1,2-1,2-2,3-1, ect . I would also like their to be a way that if their is not data entered in a group or field that it can be either easily removed, or give an error that the form is blank and not go any farther. The code attached is in my database but it does not seem to work.
Thanks for looking,
Joe

Private Sub New_Item_Click()
Dim intnewrec As Integer
intnewrec = frm.NewRecord
If intnewrec = True Then
MsgBox "You're in a new record."
a = 1 + Item
Item = a
End If
End Sub
 

Answer:Solved: Microsoft Access Increment a field

16 more replies
Relevance 93.07%

Good day,

I'm seeking assistance with glitches in my MultiValue Lookup Boxes:

Scenario No. 1:

The MVL field is sorted in alphabetical order, last name first.

Example of names in MVL:
1. Michael, Christopher
2. Michael, Jones

When I search the MVL field for Michael, Jones it returns the record for Michael, Christopher. Even if I manually scroll the MVL field and click on Michael, Jones, I’m still presented with Michael, Christopher’s record. It appears the MVL field is only searching/looking at the first occurrence of Michael.
Scenario No. 2:

The MVL field cannot locate records with an apostrophe in the name. For example: Jn’Marie. Do I need to omit the apostrophe?

Thanks for your assistance.
 

Answer:Solved: Access 2010 MVL field Errors

16 more replies
Relevance 93.07%

Hello. I need help (in more ways than one but I digress ) with an Access interface. How do I "lock" or "dim" fields?

If Frame264.Value = 1 or 3 Then I need these fields locked....

NICUTotal (text box)
NICU Total (label box)

And

OBTotal (text box)
OB Total (label box)

Any help you can give would be greatly appreciated!!
 

Answer:Solved: Help with dimming/locking a field in Access - Please...

8 more replies
Relevance 93.07%

I did a quick search and was not able to find anything that I was looking for...

Here is what I have..

I am using Access 2003 and I have a table that tracks file names. I am looking to do a Find & Replace on anything before the "/".

For example: Find and replace you can use "*/" and replace with "". That will remove anything before the "/".

Ex: /My Documents/Downloads/Files/testing.xls
/Documents And Settings/Users/Desktop/TestFile/ReadOnly/testing.doc

So what I want to end up with is just:

testing.xls
testing.doc

I have tried Replace(), that only replaces the text and will not remove it, so I am not sure where to begin on this one.

Thank you in advance!

Edit:

The closest thing I have come up with is:
RTrim(Right([F1],InStr(2,[F1] & "-","-",1)-1))

But that does not stop at the "/" and sometimes it removes part of the file name...
 

Answer:Solved: Access - Removing Data in Field

9 more replies
Relevance 93.07%

Hello All,

I am fairly new to Access and can really use some help here. I have a form with a field "PO Number" that needs to be a required field before my user can use the print button.

All the other ideas I have seen seem to be very complicated to me as I do not do VBA and I am using a button with a macro that saves the form and prints the report. The form is a Sales Ticket.

All I want is to require the field "PO Number" to be required before it will save or print the report from the form.

Thanks in advance for any and all assistance.

Scooter1959
 

Answer:Solved: Required Field in Access Form

16 more replies
Relevance 93.07%

I have inserted code into the BeforeUpdate event that tells a user if a particular field, which has been skipped, is required. The code works fine, but when you click the "OK" button so that you can proceed to input the required field, you get (1)a window that tells you "You can't save this record at this time.....do you want to close the db object anyway?" You click "no" so you can proceed and you get (2)another window titled "Macro Single Step" . I then have to "X" out of that window before finally being able to return to my form to input the required field.

Is there any way I can prevent (1) and (2) from displaying so that the user can simply go from "OK" back to the input screen and enter the required field?

I am using the following code"
Private Sub Form_BeforeUpdate(Cancel as Integer)
If IsNull(Me![niche]) Then
MsgBox "Niche number is required"
Cancel = True
End If
End Sub

The form uses a "Close Form" button.

thanks for any help.
 

Answer:Solved: Mandatory field in Access 2010

16 more replies
Relevance 93.07%

Hi all,

I'm trying to create a report based on a small database of info imported from Excel. What I want to do is have the report totalled by month. I created a new field for the main table with an update query that added just the month name (don't know if I need that to total by month or not, there is a date field as well). I've uploaded the database to give a better example.

What I want to end up with is a report that shows a Monthly total for each Source in the database. There are five sources, so this should be 60 different totals. There is a field in the database called 'galTKN', this value should appear in that summary as well. The value will not change and is the same for every record. I was debating creating an extra table for this, but it could change in the future.

Let me know if you need any other information. TIA.
 

Answer:Solved: Access: group by field in a report

6 more replies
Relevance 93.07%

Hello,

Using Office XP Im trying to create a query that will ask for a message, then append this message to the end of a field on records where a 2nd field (a tick box) = Yes.

The code at the moment looks like this...
Code:

UPDATE [TABLE] SET [TABLE].[FIELD] = [FIELD]+". "+[:Enter Message]
WHERE [TABLE].TICKBOX=Yes;

This seems to work apart from where FIELD is null, on these occasions the FIELD remains blank.
TIA
Slimboy Fat
 

Answer:Solved: Append Message To Field In Access

15 more replies
Relevance 93.07%

Hi Everyone!

I'm working in Access 2003. I have a table (pulled from a Cobol program) that has active user info. The user names (first & last) are in 1 field. In Access, I want to separate the first and last names into 2 separate fields: Fname & Lname. The names are currently displayed like this:

Mouse, Mickey

I want this to be done in a query so that I can export the data into a spreadsheet for employees to use.

The left, right, trim, mid functions don't seem to work for me, unless I don't know the full extent of their capabilities.
Thanks to all who can assist!
 

Answer:Solved: Extract part of a field, Access 03

I have this database from a previous post that does it in a form, which you could use to save the data in a table and then just use a simple select query for your export.
It dosn't use commas as seperator, just spaces but I am sure it wouldn't take you much effort to convert it.
 

2 more replies
Relevance 93.07%

Of all of the years I've worked with Access, I've never needed to do this; well here I am being asked to get a list of field names from a table. I'm aware of a few ways (set a list box to be field list, and using the db analyzer). I also found some code that doesn't work to read the database fields and dump them into a file (I'll paste to this note - apparantly I'm not running the correct version of ADO to use it).

Any help would be greatly appreciated. It dies on a "User-Defined type not defined" error inside the funtion at "Dim MyDB As New ADOX.Catalog"

I found the code on a developer article, but not sure I'm implementing it correctly. I feel like I'm out of my league on this one, but willing to give it another shot.

Thanks in Advance

Jim
Code:
Option Base 1
Public Sub Enumerate_Table()
On Error GoTo ERROR_PROC
Dim aryFields()
Dim lngCount As Long
lngCount = 0
strInput = InputBox("Please enter the name of the table for which" & vbCrLf & _
"you wish to list FieldNames & Descriptions." & vbCrLf & vbCrLf & _
"Output will be placed in tab-delimited text file.", "Table Name Input", "MainTabl")
If StrPtr(strInput) = 0 Or Len(strInput) = 0 Then
Exit Sub
Else
strSQL = "SELECT * FROM " & strInput
Dim Adofl As ADODB.Field
Dim rs As Ne... Read more

Answer:Solved: Getting Field Names from Tables in MS Access

6 more replies
Relevance 93.07%

I have an access main form that contains a subform. The subform is setup to display in datasheet view.

On the subform there is a memo type field. Is there a way to have the memo field to word wrap?
 

Answer:Solved: Access Subform - Memo Field

I don't think you can in Datasheet view. You could try setting the Can Grow property to yes.
 

2 more replies
Relevance 92.66%

Hello everyone,

I have the current formula

CompPassFail: IIf([specifiedComp]>[PCcomp],"Fail","Pass")

This works O.K, but I am being told that it should pass or fail if is within -/+3percent. Both field are percentage fields, where[specifiedComp]is the main number that [PCcomp] has to more or less align to.
 

More replies
Relevance 92.25%

Hi,

I'm trying to set up a pivot table on some data and when I select the data and click on Insert Pivot Table, it opens a new sheet in my workbook for the new Pivot Table, but there is NO Field List to drag fields into the pivot table. And, yes, I do have the Pivot Table box on the left selected, but NO Field List.

Thanks for the help
 

Answer:Excel 2010 - No Pivot Table Field List

7 more replies
Relevance 92.25%

I have a dynamic range set up for a pivot table which works well but I was hoping to also make the Field Names dynamic. At the moment whenever the field name changes (as I have the source data headings dynamic) the fields all drop out of the pivot & have to be added again.

This is time consuming as there are many columns of data to be added, does anybody know of a way to make the Field Headings in the pivot match the source data without dropping out of the pivot when the heading changes? It is always the same columns of data that are needed in the pivot.

Thanks!
 

More replies
Relevance 92.25%

In my database I have what I call the main form. This form , among other things, contains a persons social security number [SSN]. I have placed a control button on this form that allows for me to jump to a hyperlink and perform a search of a second database through ASP. Now, everything is working fine but what I would like to do is be able to "plug in", via code, the [SSN] field data into the static portions of the hyperlink string. The SSN would have to be taken from the currently viewed record on the main form. The code for the button, as shown below, has a dummy SSN of 999-99-9999 (in orange) and this is the data that I need inserted from the [SSN] on the main form.
_____________________

Private Sub Command215_Click()
FollowHyperlink "http://1.1.1.111/Prog/ProgWeb.aspx?Action=Search&Database=database name&SearchType=DocumentProfile&SearchValue=999-99-9999&ProfileFieldName=Document%20Title"

End Sub
___________________________________________________
As the code stands, it works as long as I manually place an SSN in there. Any suggestions that will allow me to take the SSN from the currently viewed record in the main form and place it between the two static portions of the hyperlink?

Thanks in advance.
 

Answer:Solved: Access: Insert field data into hyperlink

7 more replies
Relevance 92.25%

I have a field that is date/time, formatted to display the long date (which starts with the day of the week). This has been extremely useful for some append, update and delete queries.

However, I would like to be able to do an update query based on the day of the week for each record. I know that the date/time field is stored as a number so I can't search for any string of letters. But, is there a way to take the long date format and copy what's there as a string so I can then extract the day of the week out of it? Otherwise, I guess I'm stuck with adding a separate field for day of the week, and manually inputting it. Seems like such a waste.
 

Answer:Solved: Access, Date/Time Field to String -- is it possible

7 more replies
Relevance 92.25%

I have a comment field that sometimes has comma's in them. The issue is that when we change this table to a .csv file the comma's cause issues. I have tried to Replace - but I am having a hard time with it.

The query that puts this field to the table currently has this code.

AuditorComments: Last((IIf([DisplayDatabase]![Add Comments for Display]="0"," ",[DisplayDatabase]![Add Comments for Display])))
How do I either remove comma's ore replace them with something like a dash?
 

Answer:Solved: Access - remove comma's in a Comment field

does this help
http://www.techonthenet.com/access/functions/string/replace.php
 

3 more replies