Wrox Programmer Forums
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old May 29th, 2007, 08:21 AM
Registered User
 
Join Date: May 2007
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to slim
Default Auto populate/look up

Hello all,

Im relatively new to access and i think ive bitten off slightly more then i can chew!

At the moment there is a database which has several tables e.g.
"physical records"
"input data 1"
"input data 2"
where the physical record table has got 20 or so records, of which in all the input data tables has one column/list of data e.g. a user name or a product, which is used as reference in the physical records table. this was setup fine.

however,

now one of the input data tables has been changed to have 2 columns so it has gone from "process" to "process" and "template" and i have been asked so that when a user is making a new record (via a form) on the "physical data" table and selects a "process" (linked to the relevent input data table) and it auto fills a field in the physical data table with the relevent "template" AND to be then able to edit that same field with more information relevent to that exact record.

In other words to try and make it easier to understand. The template field is a list of things to check depending on what process they have followed, and different processes require different checks. E.g. for the process to make a new customer: check abc, for the process to amend a old customer: check xyz.
The field which displays the checks also needs to be able to be edited by the user making the record so they can say yes they have checked abc and then enter any other information about the record.

so the field shows up automaticaly "check abc" then the user can edit the field to "abc checked, customer xxx has been created"


Sorry if my explination is a little long winded, i am really stuck on this and would be very grateful of any help.

Cheers
 
Old May 29th, 2007, 10:34 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

I am not sure I understand what you are trying to do here.

Do you need something modified? If so, what is it, the combo box, a form control, a sub form control, form business rules, etc?

The only thing I can see for certain is that you need to make a text box editable, in which case you select its properties, select the data tab, and make sure the control is not disabled or locked.

Did that help? If not, please post the specific tasks you would like explained.

mmcdonal
 
Old May 30th, 2007, 04:09 AM
Registered User
 
Join Date: May 2007
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to slim
Default

Right if have two tables

Table A, which has the Fields 1a and 1b on it
Table B, which has the Fields 1a,1b,2,3 and 4 on it,
In Table B, Fields 1a and 1b are combo boxes linked from Table A,
So Table A actualy contains the data, Table B just looks up the data.
Table B, Field 1a is a working combo box
I am trying to make it so. . .
When using Table B, what ever option is selected from 1a, Field 1b automaticaly fills with the data from Table A, Field 1b
(e.g. if field 1a is a list of countries 1b would be contients, so if you select England from 1a, 1b auto displays Europe etc)
Then once Table B, Field 1b has been filled with the data I would like it to be able to be edited by the user without changing the data from Table A, Field 1b.

I don't know if this helps any, as its really hard to describe what it is that the field needs to do.

thanks

 
Old May 30th, 2007, 06:39 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Oh.

So here is what you do.

Create ONE combo box that looks up to table A, fields PK, 1a and 1b. Use the wizard for this.

The wizard will bind to your PK column in table A (maybe that is 1a, but might be an autonumber field) and hide it by making the column width 0". Then it will display the other two columns.

Change the column widths from:

0";1";1" (probably default)

to

0";2";0"

This will hide column 1b.

Then create a text box bound to table b, field 1b (WHICH SHOULD NOT BE BOUND TO TABLE A!!!) on your form.

Then on the after update or on change event of the combo box, put this code:

Me.txtTableB1bTextBox = Me.cboTableA1a.Column(2)

This will put the text from column 2 (0, 1, 2) in the text box, and it will be editable.

Did that help?

mmcdonal
 
Old May 30th, 2007, 06:39 AM
Registered User
 
Join Date: May 2007
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

You can set the OnChange event for the field 1a combo box to repopulate the 1b result. This isn't hard in VBA, you should be able to do it without VBA though.

 
Old May 30th, 2007, 06:41 AM
Registered User
 
Join Date: May 2007
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

or what mmcdonal said!

 
Old May 31st, 2007, 03:58 AM
Registered User
 
Join Date: May 2007
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to slim
Default

Hi all,

Thanks for the help, however i have tried this but all i keep on getting is
#name?

i have put this in the forms "after update" event.

my code is:
ME.txtDetailsofChange.changes = ME.cboFunctionalArea.Areas.column(1)

have i just put a wrong dot somewhere or somthing as silly?

just incase it helps

Table A is called "FunctionalArea"
Table B is called "Detailsofchange"

In Table A, Field 1a is called "Areas" and Field 1b is called Checks
In Table B, Field 1a is called "functionalArea" and 1b is called Changes.
In field A, 1a is in column 0, 1b is in column 1
Thanks all in advance.





Similar Threads
Thread Thread Starter Forum Replies Last Post
GridView auto populate stonesbg ASP.NET 1.0 and 1.1 Basics 0 February 19th, 2007 10:41 AM
Populate Auto-Number Field JeffGirard Access 2 August 26th, 2005 06:49 AM
Auto Populate fields in a form mnemec24 Access 8 March 9th, 2005 02:11 PM
Auto Populate a field ? mar0364 Classic ASP Databases 4 July 9th, 2004 11:10 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.