OK, I'll Try Again - Autofill And Dlookup Help PLEASE
I have been struggling with Access and the dlookup / auto lookup function.
I'll include my last post with the layout of my 2 tables and what I am trying to do
Please help if you can:
table 1 - OCMembersPayment
| transaction# | member ID | Payment Date | Payment ID | Payment Amount | Chit_checkNumber | Last Name | First Name | Notes | Ticket Number |
Table 2 - Member Roster
| Unique # | Member ID | FirstName | LastName |
Ok, I have eliminated any relationship between the two tables
What I want to happen is this:
When people put data in, the first data they fill in is member# - ex 2515 and then hit tab to move to the next field
When they hit tab, I want the expression to look at the member roster and find that #. when it does, I want the first and last names to get writen to the fname and lname fields on the form.
date is automatically filled in and Payment code (accounting code) is automatically filled in - default #
currently, this is the dlookup script I am trying to use and I get compile errors:
Private Sub MemberID_AfterUpdate()
(it is currently on the the Member # field on the form).
Later, I want to be able to ADD new members as they show up to the member roster table. So if the lookup does not return a name, once the transaction is filled in and saved, it will put the name in the member roster table. First things first though
1. After my relationship table is built, is it possible to have some tables automatics fill in according to what another field in the table is inputed? EX: I have a Ordertable(orderID,itemID,Itemdesc), and a Inventory table(ItemID,Itemdesc), When you select an Itemid to put in the order form, the order Description from Inventory would appear in the Itemdesc of the order form.
Is this possible? If so how?
2. In tables, is it possible to make a cell = to something? So like if I wanted to add a subtotal and a final total after tax, I could use = * .08 or something?
I am trying to self teach myself and I am sure I am going about his wrong.
I have a form with a fillable text box called ProcessCode, a drop down box called CostContainerSize, and a fillable text box called disposal cost. Their are dozens of possible process codes and container sizes of 55,30,15,5,P,Y etc... I want the disposal cost box to auto fill based on Process code and disposal cost. I wrote the following in the control source IIf([ProcessCode]="LF" and [CostContainerSize]="55","$65.00") THis works to auto fill, but obviously only for that particular set of parameters. I have tried adding "or " comments to include other sizes for that particular process code with limited success. I have tried IIf([ProcessCode]="LF" and [CostContainerSize]="55"or"30","$65.00"or"$50.00") among other variations.
I created a form from a table (maintbl) with a combo box. The combo box retreives information from another table (model#tbl)and autofills it in the fields below. the information that is autofilled does not automatically get entered into maintbl. Theres probably a good reason for this but i cant figure it out. Anyone?? Im not very good with code but if thats what it takes thats what it takes.
Hello everybody! Im new in Access as well as in this Forum! Glad to be here :) Im currently developping a small application in Access 2003 and here is my problem: I am trying to autofill the value of the field OrderID in this form : "FRM/SupplierID/CurrentYear/OrderNumber" Where the SupplierID is charged once the SupplierName is selected from a combobox and the OrderNumber entered from another textbox. Note: SupplierName values are from other table: Suppliers
Hello, I am creating a helpdesk support database. My question is for my Customer Form. As I tested the form I realized that one of my Combo Boxes may not work cause the name may not be the same when the customer calls. Let me Explain:
The Combo Box is query from one table with 5 Fields:
CompanyID > PK Company NumberID > unique Number Company Name > Short Name Company Number > telephone Number Company category > Category Company belongs to
So, when the customer calls they may give another name for the company or just an abreviation.
what I did I created a pop up form which displays all records showing the NumberID; Name; Number; and Category, with a lookup txtbox to search for the customer by typing the company NumberID on a continues form.
Now, Here the agent can actually do two things: One, the agent Can Update the company Phone Number and Category. the second thing will be to either double click or select the right company and autofill the combo box or should I used a textbox instead of the combo box in my form.
P.S. Right now I using a Combo Box.
Thank you. Any help in the right direction will be great.
Can anyone please help:confused: . I am setting up a database for events for local companies, I have an events table (event title, date, time etc) and a company table (name, address, postcode, tel, etc) They have a one to many relationship, with the events table being 1 and the company table being many.
I have a main form with the event details and I want to add a subform for companys, but would like this form to autofill the companies address and contact details when I enter the company name. Is there a way you can do this? :)
Hi there everyone. Please could someone help me with a problem! I have a form running from two qry's deceided by grp selection, written in the back end with VB. My problem arises when I attempt to autofill txt fields with the record connected to the selection made in the cbo box. Can anyone give me advice (all of which will be greatly appreciated) and can anyone see a good way of doing this. Thank you for taking the time to read this and I look forward to any guidance. Chris.
I need some help with getting my form(form 2) to autofill from another form(form 1).
Now i dont want to use combo boxes or command buttons as i only want to use the keyboard and not the mouse.
Basically when a customer phones up to get prices they tell me there account code( this is in the customer details table) and i enter it into a text box on form 1 and after pressing enter it opens up form 2(this bit i can do) but i cant get it to open the form and display the correct details from the table on the form.
p.s why is it access books tell you everything except what you want to know.
Hi sorry I am only new to this, I am trying to learn as I go so please excuse my ignorance. (I have done some searching on this forum but am just getting more confused) Anyway I have a form which is based on a table that holds all the info for orders placed by customers. It holds info on all the orders plus some info about the customer
Example of Orders Table
Order ID Customer No Suburb State Item Number Qty Ordered Description Length Weight Order Type Day Received Last Dispatch Day Actual Dispatch Date Comments Date Due at Customer
On this form I would like a combo box which is linked to a Customer table which has all the details regarding the customers. When I pick the customer I would like all the other fields relating to the customer on this form (Customer No, State, Suburb) to be auto populated to save entering data individually.
Example of my Customer Table
CustomerID Customer Name Customer No (not unique) State Suburb Customer Factory Rail Terminal code Destination Rail code Destination Road code Cust Code
I manage a self-made database of property deeds for my business. Since it often happens that there are multiple owners and multiple buyers of these properties, and that many of the owners and buyers are the same people from time to time, I think I can manage this database more efficiently by using subforms that autofill when I start to type a person's name. I hope this is possible.
My main table and form are based on the deed description. Each deed will have unique buyers and sellers and each buyer and seller can be on many deeds. Thus far I have created tables and queries for sellers and buyers. The seller subform is set up and can accept entries in the form view. When I scroll from record to record, the names seem to show up where they belong, so I'm happy with that. The problem is that when a name comes up a second (or more often) time, I end up typing the entire name in again. This sort of defeats the purpose of having related tables. How can I get it to recognize that the name is already there and perform an autofill for me? What am I missing?
What I need to do is two fold. I have a table in which I track clinets coming in and using our services.
Question 1: I want to be able to have a a date field default to the date I first type in when I open the form and remain there in all subsequent records till I change it. The data is only entered once a week and we usually have 400 client visits per week with about 75 - 120 per day. So it kind of gets repeatitive to keep typing in the date. I am aware of the CTRL+', but the staff who use this database do not even want to do that.
Question 2: Next problem....How would I create an autocorrect function that will automatically fill in a name...i.e. I begin to type jo and it will automatically fill in the hn...but I want the values to come from previously entered data from past records, similar to the way Excel does it in spreadsheets.
Hi! My first post here! I am quite an amateur at database programming.
I am developing a form to input data about paint samples into a database which represents a sample library.
When the user starts to input data about a new sample into the form, they first have to choose a 'base metal' from a combo box on the form. Based on this 'base metal' selected, after all other fields have been filled in, i want to generate a 'catalogue code' for that record (The 'catalogue code' is an alpha-numeric code). Both 'base metal' and 'catalogue code' values are entered in two fields in the table.
for this i want to have a command button 'Get Catalogue Code' which runs a code to compile the 'alpha-numeric' text using two different tables. I want this text to be inserted into the 'catalogue code' field in that particular record and a message box informing the user what that text is.
I can figure out the code to compile the text and the message box. What i dont know is how to insert the text into that field in the table.
I am willing to provide any more information, including my database to anybody who can help! Thanks in advance!
I am attempting to use a combobox to fill several other unbound textboxes on a form. The syntax I am using is simple, (SAMPLE) Private Sub Combo48_AfterUpdate() Me.SSN1 = Me.Combo48.Column(0) Me.Rank1 = Me.Combo48.Column(7) End Sub
This command is based off a query I set up combining 2 main tables. The problem is it only seems to be working for column (0) and (1), anything above that and I just keep coming up blank. Is there a better way to do this or does anyone know what may be limiting the above command from displaying any column above (1)?
OK, the syntax below works awesomely in a form. The SSN number is pulled from the main table and dropped into a bunch of text boxes. The only text box that is bound is called [sub#] and is stored in the supervisors table, 6 sub's per supervisor and the supervisors SSN is a child of the main table (tried to keep redundant data to a minimum). Now I need to know if there is any way to perform a similar trick in either a query or report using the [sub#](SSN equal to that of a main table entry) field to call up the record from the main table or from a query to use in a report?
Example of working syntax from form... Private Sub Combo48_AfterUpdate() Rank1 = Combo48.Column(4) Sub1 = Combo48.Column(0) Last1 = Combo48.Column(1) Rank1 = Combo48.Column(4) First1 = Combo48.Column(2) MI1 = Combo48.Column(3) EPR1 = Combo48.Column(5) Rating1 = Combo48.Column(6) Next1 = Combo48.Column(7) End Sub
I have a table called and form called insurance it has the following Insurance name, address, city, state, and zip as text boxes. I have a form called patient info and on that form i have a combo box called Primary Insurance i also have a text field under neat primary insurance called insurance address.
I'm trying to display insurance name address city state and zip in the text field once i select the insurance from the combo box. i have the following code
in the after update field in the combo box
Me.txtinsuranceaddress= me.primaryinsurance(name of Combobox)
The problem i'm having is that when i select the insurance from the combo box in the text field it only displays insurance name and not the adddress city state and zip
I have a button on the school form that opens the venue form. I want the school field that is on the venue form to auto fill with the same information that is in the school field on the school form. Any help would be awesome.