Modules & VBA :: Completing Fields On A Form With Matching Data From Previous Records

Jun 4, 2015

I'm trying to make it a little further with my new call answering database at work.

Basically it consists of a main form that has a button leading to a call answering form for each business that we represent. it also has a text box that displays the number of the caller when the telephone picks up, the correct call answering screen is also popped by the telephone software and a macro whenever the phone is answered.

I've got code in place that automatically puts the date and time in the correct field and also copy the incoming telephone number from the main form into the caller number field on the call answering form.

I'd like to take this automation a little further now and get the form to autopopulate the caller details if that caller has called before (we get a lot of calls from the same people) so i'd like to make the form search the table it's linked to for the incoming phone number and to fill in the name, email, company etc... for the caller according to the previous record.

The code for the 2 forms i've currently got setup (the switchboard and one call answering screen) are as follows

Switchboard:-

Code:
Option Compare Database
Dim WithEvents MaxxCom As Metro_MaxxCom_CTI_COM_API.CTI
Private Sub cmd_onnet_Click()
DoCmd.OpenForm FormName:="On_Net_Communications"
End Sub

[Code] .....

View Replies


ADVERTISEMENT

Modules & VBA :: Event To Match Up Previous Entries And Insert Matching Data

Nov 19, 2014

The next stage I'd like to include is a match and enter pricing details. We get a lot of repeat work that is entered onto our system, due to the nature of the work we do each item is then checked individually. There is a portion of this that could be automated.Our order intake table has an amount of information, various bits of which are supplied by various customers, but not always all of the information is required or given.

Id like a price to be copied from a previous record where the "shots" field matches a previous entry.The other fields that would need to be similar are the "coverage" "drawing no" "pattern no"..But only when these fields are populated, we often, but not always put a - in these fields where the information is not given. I think this may be bad practice, but it shows us the information has not been supplied, and not just missed from the order.

To sum up, when an order is completed i'd like to have an afterupdate event that searches the previous records, where cover, drawing no / pattern no are *matching* and the shots do match, then copy the price of a matching record, where that record is within 12 months of today (using the date_rec field) and then tick an "automated_price" field so i know the price has been generated by the system.

View 1 Replies View Related

Command Button To Open Another Form - Matching Specific Data Fields

Aug 25, 2011

I have a subform in which I want to put in a Command Button to open another form.

I used the wizard as per normal, but when you come to matching specific data fields, there is nothing in my left hand column of the sub form. I have checked the record source property and it is bound to the correct table.

I have read that this is an issue with Access 2007? Is this the case or am I missing something obvious here?

View 3 Replies View Related

Non-Editable Fields And Auto Completing Fields

Jun 28, 2006

Hello all,

First post :) found some great tips on here. Anyway here is my story and question.

I am new to Access development. I know what I want to do, I just can not find a way of doing it :( .

1) I would like my form to only be editable when an edit button is clicked. I am not sure if I can do this on a form level or if I have to change each individual part. Any ideas?

2) I capture some clients details, title, first name and last name. I then need to merge the above 3 fields into one to create a 'policies in the name of' field. Is this posisble? I have tried using the initial value expression but am having no luck. Any ideas?

Thanks all :)

View 2 Replies View Related

Combo Box Completing Multiple Fields Help

Dec 2, 2005

Ok... big time newbie/hack here. I have a tiny bit of programming experience from waaay back with UNIX and C. Have basic database experience and understanding but no VB experience. So I appologize ahead of time if this seems really simple. I'm working on "fixing" a small access database/program for my wife's work. Very simple design.

I have a company table and a customer table. I have customer form and a company form. They may have several customers at the same company. So my design has them starting on the Customer form. They begin by filling in the Customer's Name and then they get to the Company Name combo box. They should select the Company Name from this box. If it is a new company and is not in the list they should click on the "New Company" button beside the Company Name field. Then the Company Form opens and they can add a new Company along with Company address and phone numbers etc. Then they close the Add Company Form and then can select the newly added Company form the Company Name combo box (by the way, the only way I could get this box to update with the new company name was to use a macro that only runs the Requery statement for the On Enter event... it works... I hope this is ok).

Anyway, this is where my problem arises. I need the selection of the Company Name to also pull over the address, phone number, fax number, etc. info from the Company table and complete it on the Customer form/table now. How do I do that? I think I need to do this with the After Update event with some VB code... but I have no idea how to actually pass this data. The property for my combo box is as follows:
Control Source........CompanyName
Row/Source Type.....Table/Query
Row Source......SELECT zMASCompanies.CompanyName FROM zMASCompanies;

The field doesn't have to be a combo box (if this is not the right way to do it)... I just need them to select a company name and have it fill in about 20 or 25 fields from the company table/form into the customer table/form.

I did try searching the forum and found a few things but nothing I found was very clear (especially since I am not sure I am even heading down the right path). Any assistance would be greatly appreciated!!!

Any help please????
Thanks,
Jeff

View 7 Replies View Related

Queries :: Show All Fields With No Matching Records - One Table

Aug 14, 2013

How to get the following results using 1 table:

Field1 ID is an auto record ID, field2 ID is actually field1 ID assigned that record, in other words record 1 has a roommate (record 5) assigned to it, record 2 has a roommate (record 4) assigned to it

Table A

Field1 ID Field2 ID
1 5
2 4
3 6
4 2
5 1
6 8

Results records I'm looking to display for would be:
1 5
2 4
3 6
6 8

I only want to display all fields for records, but I don't want to show their matching record, so I want to display record1, but not record 5 because record 1 has record 5 as a roommate, want to display record 2 but not record 4.

View 5 Replies View Related

Append Data To Field For Matching Fields

May 18, 2005

Hello,

I have a few tables containing different exam data for students - and a unique identifier with each. I need to add another unique identifier (which is already specified from another system).

Now, can I use the existing identifier (UPN) to automatically put in the new identifier into a new field (AdNo).

If that makes any sense at all, I will be surprised...thank you!

View 1 Replies View Related

Forms :: Matching Values Of Fields With Data In The Table

Oct 20, 2014

I have created a login form and create user page form, the tblListofUsers has two columns, UserName and Password, on the log in form when a user enters username and password, it should be matched with the tbllistofusers if true then msgbox"Welcome" else Msgbox "wrong username or password", I have written following code but gives error

Private Sub Command5_Click()
If DLookup("UserName", "tblListOfUsers", "UserName = " & Forms![loginpageForm]!User) And DLookup("Password", "tblListOfUsers", "Password = " & Forms![loginpageForm]!passworduser) Then
MsgBox "You welcome"
Else
MsgBox "Wrong username or password"
End If
End Sub

View 8 Replies View Related

Forms :: Search Multiple Fields In Table And Open Matching Records For Editing

Dec 10, 2014

Create form to search multiple fields in table

Return records that match search

Open the record that you want in Form View for editing

View 1 Replies View Related

General :: Merge Two Fields From Two Databases That Have Matching Two Data Ranges

Mar 25, 2014

I have two databases (files). Each file has a format like the one below.

File 1:
chr start end
chr1 1000 2000
chr2 3000 4000

File 2:
chr start end
chr1 1500 3000
chr2 3750 4000

I want to be able to merge the two records together if the field chr matches between the two files plus if there is an overlap between the start number and end number from each file. For instance the first record from each file would match because the range from 1000 to 2000 of file 1 has numbers consisting of 500 numbers (1500-2000) that are also present and overlap in file 2 (1500-3000). I possibly cannot use < or > since the ranges from each file will vary to different degrees. Perhaps there is a between function that might work...

View 7 Replies View Related

Forms :: Automatically Insert Data To Another Table By Completing Checkboxes

Jun 1, 2015

I'm on my way on creating a simple Database for a company. This database contains several tables, one of the tables will record information about the training that had been completed by each employee. There are about ten sections of training that should be completed.

For instance, I have two tables called 'Development' and 'CSA_Lisence'. 'Development' is the table that record the information about training which containing ten checkboxes (which represent ten sections of training) and CSA_Lisence will be automatically requeried when all of the checkboxes on Development are fully checked.

Here's the step I've been worked on :

First, I made a function called 'CheckCompletion' to ensure whether all the checkboxes are checked :

Code:
Public Function CheckCompletion() As Boolean
Dim blnComplete As Boolean
Dim strCompletionSummary As String
strCompletionSummary = Basic_Inspection & Certifying_Staff & Safety_Management_System & Regulation_Part_145 & Part_M & EWIS & Fuel_Tank_Safety_Level_2 & Dangerous_Goods & Human_Factor & Basic_Supervisory_Training

[Code] ....

Second, I made a function called 'UpdateEmployee' to handles if all boxes are checked :

Code:
Public Function UpdateEmployee()
Dim emp_numb As Long
Dim emp_name As Long
Dim strsql As String
emp_numb = [Forms]![development].[employee_number].Value

[Code] ....

Then, I put this code on every checkbox's after update event (example only) :

Code:
Private Sub Basic_Inspection_AfterUpdate()
Call UpdateEmployee
End Sub

The problem is, nothing happened with the tables. However, when I managed to remove the 'If checkCompletion' condition, it worked and the 'CSA_Lisence' is requeried, but I will have ten multiple records with same contents (I just need one record per employee). I guess there's something wrong or missed in my code. Or i need to remove something?

View 7 Replies View Related

Modules & VBA :: Training Matrix - Matching Listbox Selections To Table Records

May 6, 2015

I have a training matrix that lists employee names and certifications on various operations. The objective is to choose an operation and run a query to display everyone who is certified on that op. There are additional variables.

Code:
Name EMP ID OP1 OP2 OP3 OP4 OP5
-----------------------------------------------------------------------------
John Doe 526261 C C C
Bob Doe 555622 C C C
Sheila Doe 066600 C C C

Okay that looks about right for the data itself. The listbox has all the ops, you choose an op and hit a button and it goes and finds everyone who has a 'C' in that op column and pulls their record.

View 14 Replies View Related

Modules & VBA :: Database With Thousands Of Records - Open Image Matching A Field

May 6, 2015

I have to create a database with more than ten thousand records. There is a field on which image by clicking the image to be displayed with the default program based on the image on which you clicked.

Code:

Private Sub Campo1_Click()
Dim L As Long
L = ShellExecute(0, "Open", """" & "C:UsersNickDesktopDocumentiPicturesDioDiego.jpg" & """", vbNullString, vbNullString, 1)
End Sub

When onClick event happens on the image is displayed the same image regardless of the record on which you clicked. How do I change the code to open the image of the field that was clicked?

View 2 Replies View Related

Modules & VBA :: Data Matching Between Table And Text File

Feb 6, 2015

I have a table. Also i have a text file which some of the fields are matching with my table fields ( lets say field A and B ). Now, i need to do compare of these A & B of my table against A & B of the text file and give a result as follows;

1. "Field A" not in the table but in the text file (un-matching data to be shown )
2. "Field A" not in the text file but in the table (un-matching data to be shown )
3. "field B" mismatches

View 9 Replies View Related

Forms :: Matching ID Fields To Auto Fill Form Field

Aug 30, 2013

The table ProductInfo has an ItemID, and its corresponding Quantity.The table Orders has a PO column, an Item# column, a OrderQuantity column, and the column Item#CurrentStock which holds the Quantity of the selected item, for 3 items.I have created a form that has a ComboBox populated with the ItemID's from ProductInfo, that when selected populate the corresponding Item# in the Orders table.

I would love if the Quantity(stock) from ProductInfo would automatically fill the Item#CurrentStock field on the form when the ItemID is selected from the ComboBox.But all of the fields only relate to Item# from the first ComboBox!?!?I have tried =dlookup,

View 3 Replies View Related

Modules & VBA :: Using Previous Records To Find Totals

Feb 18, 2014

Basically what I have is a form where operators can input certain information about a production process, in particular the kg input which has gone into the 'basket'. However this is divided by customer order number and sometimes more than one customers order is placed into the same basket. This means that the weight of the record is not representative of the weight in the basket, and it is the weight of the basket which determines efficiency in the process.

Each basket is designated a 'Disc Number' which is put in the basket so the parts contained can be identified after the process. What I am trying to do is right a code, whether in my table or form (or somehow creating a query) which compares the disc number of the previous record (or previous 2 or 3) and if they are the same the totals are combined. Each record the user places has a hidden ID which keeps them in order, and the records with the same disc number will always be entered after one another in that sequence.

How would I be able to code something to deal with this and where would I put it?The table in question which contains the records is called Thetatbl and then form it links to is Thetafrm. The weight of each record is contained in Orderkgtxt.

View 7 Replies View Related

Queries :: Enabling / Disabling Fields According To Previous Data Entry

Aug 6, 2013

I need to create a form to enter data from a survey, and ideally some sections of the form would only be completed when the answer to a first question is "yes". How to do this enabling/disabling of fields for data entry?

View 3 Replies View Related

Forms :: Display Data Based On Previous Record But Not All Fields

Jan 22, 2015

There is a form which the user fills in 8 fields and saves it. Once saved, a new record opens.

However, I want to modify the save button to save the previous record and display a new record with only a limited no of fields to display from previous record, say 4 fields display from previous record, and the user fills the remaining 4 and saves it. Thus, the save cmd should continue to display 4 fields from the previous record.Already tried below code for save button but it displays all data of previous record as it is, doesn't display blank space for combo.118 and text2

DoCmd.GoToRecord , , acNewRec
DoCmd.GoToRecord , , acPrevious
Combo118.Text = ""
text2.Text=""

View 1 Replies View Related

Modules & VBA :: Subtracting Value On Current Record From The Value Of Previous Records?

Jun 5, 2013

I want subtrack a value on the current record from the previous record and display the result in text box in subform datasheet. This is done during data entry. The calculation works most of the time and randomly it returns the wrong value. I have the code in an after update event after I enter a number. Here is what my code looks like:

sysHopSpacing = Abs([sysHop1] - DLookup("[sysHop1]", "tblSystemConfiguration", "[sysBaseNumber]=Forms![frmTempestCoordination]![frmSubSystem]![sysBaseNumber] - 1 "))

This is how I generate the sysBaseNumber

sysBaseNumber = Nz(DMax("sysBaseNumber", "tblSystemConfiguration", "sysAccountID=" & Forms!frmTempestCoordination!accAccountID), 0) + 1

I am using DMax so I can keep the subform records with the parent form record.

My results are inconsistant:

This is what I want the subform to do when subtracking from the previous record

syshop1 sysHopSpacing
5
20 15
30 10

Instead I get a result that does not make sense like this:

sysHop1 sysHopSpacing
5
20 15
30 4

I cant trace it down.

View 14 Replies View Related

Creating 2 Records From 1 Form With Different Data In Fields.....

Sep 22, 2007

Hi

I have a single form for which I need to create 2 records from in the same table.

On the second record when created I need to change some of data contained in the fields - How do I do this:

I give an example. On my form I have a tick box - if true it enables 2 text boxes and will need to create 2 records within a single table.

I need the first record to include the data contained in the first text box and the second record to contain the data from the second text box.

I have tried to use update queries but have had no luck. Anybody have any ideas?

thanks

View 6 Replies View Related

Forms :: Possible To Use A Command Button To Update Matching Records In A Form

Jul 7, 2014

Is it possible to use a command button (update) to update matching records in a form (Test)? I have a file (april.xlsx) in which the first column Cytogenetics ID (14-xxxx) is a unique identifier that matches a record in the database. The next column Result (NL-F, NL-M, F-VUS, M-VUS, A-M, A-F) and the date column next to it are what need to be updated in that record. In the database there is a field called Result that needs to be updated with
the text from column 2:

NL-F and NL-M = Normal
VUS-F and VUS-M = Variant of Unknown Sig.
A-M and A-F = Abnormal

There is also a Final TAT Date field in the database that needs to be updated with column 3 in the spreadsheet (Final TAT Date).So basically when there is a match with column 1 in april.txt to a record in the database, the Result field in the database is updated with column 2 of april.txt and the Final TAT Date field in the database is updated with column 3 of april.txt.

Cytogenetics ID Result Final TAT Date
14-0390 Normal 4/11/2014
14-0396 Variant of Unknown Sig 4/18/2014

View 1 Replies View Related

How Can I Configure A Table To Auto Data Entry Into Fields Based On A Previous Field

Aug 31, 2006

My situation is this. I have 3 tables that I have imported from my mainframe system, between these 3 tables I have the data of product code, description,supplier code, supplier name, order method, and ABC code.

I am trying to create another table that I can capture daily Out Of Stock data for products.

What I would like to do is to enter the product number in the first field of my new table, and then the remaining fields will auto populate with the correct details based on the product data stored in the parent tables that I have imported.

How can this be done?

View 3 Replies View Related

Forms :: Disabling Fields In Access Based On Data Entered In A Previous Field

Sep 9, 2013

How to disable fields in Access based on data entered in a previous field? For example if "yes" is chosen from a drop down show "Date field" if "no" is chose hide "Date Field".

View 2 Replies View Related

Modules & VBA :: Search Form Not Returning Records With Blank Fields

Nov 14, 2013

I'm creating a search form to filter out data based on certain inputs. My VBA code looks like:

Code:
Private Sub Command18_Click()
On Error GoTo errorcatch
Me.Filter = "([Experiments.Log] Like ""*" & Me.Text21 & "*"") AND ([Expdate] Like ""*" & Me.Text22 & "*"") AND ([BaseSolution] Like ""*" & Me.Text24 & "*"") AND([AddCom] Like ""*" & Me.Text25 & "*"") AND ([Test] Like ""*" & Me.Text26 & "*"") AND ([Plan] Like ""*" & Me.Text23 & "*"")"

Me.FilterOn = True
Exit Sub
errorcatch:
MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
End Sub

However, the output does not include records where other fields are blank. I have read that I may need to use Is Null but am not sure how to.

View 3 Replies View Related

Modules & VBA :: How To Use Form Fields To Fill Numerous Records Under Same Column

Feb 2, 2015

I am using .FormFields to fill a quotation template in Word, but the subform I am retrieving the data from has rows of records under each column and I need to send more than just the first row to fill the required bookmarks in Word.

The code I'm using is

Set doc = appWord.Documents.Open("S:TemplatesQuotation Template - Test.doc", , True)
With doc
.FormFields("FirstName").Result = Me!FirstName
.FormFields("FirstName2").Result = Me!FirstName
.FormFields("LastName").Result = Me!LastName
.FormFields("LastName2").Result = Me!LastName

[Code] ......

View 4 Replies View Related

Open Form Command Button Fills In Text And Date Fields From Previous Form

Apr 27, 2005

I currently have two forms: frmE_SAFind and frmE_SAOrder

frmE_SAFind shows results from a query including fields [txtIDPO] and [dtmDate]
Example:
IDPO Date
btnOpnFrm 6543 2/1/05
btnOpnFrm 5681 1/1/05

frmE_SAOrder shows order details including [txtIDPO] and [dtmDate]

I have a open form command button set up on [frmE_SAFind] that opens [frmE_SAOrder]. Is it possible for me to modify its properties so that when the open form command button is clicked, the order details in [frmE_SAOrder] will represent the order that the user is selecting via the btnOpnFrm command?

Example: If I click btnOpnFrm for 6543, [frmE_SAOrder] will show me PO 6543 details.

Hopefully I made myself clear enough to understand. Thanks for your help!

View 1 Replies View Related







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