Quick Links ▼

Customer: Sign in
Delegate: Sign in
Trainer: Log in

Forum home » Delegate support and help forum » Microsoft Access Training and help » access+training+london - Look up fields/imported data

	98.1 % recommend us
	More live stats...

The UK's most regular
instructor-led training courses.

Training information:

access+training+london - Look up fields/imported data

Resolved · Low Priority · Version Standard

Reply Mon 21 May 2007, 12:01Delegate Sin said...

Sin has attended:
Access Intermediate course
Access Advanced course
Access VBA course

Look up fields/imported data

Due to the fact that the database I have set up is used by both remote and office workers, I use forms and import excel spreadsheets to keep the data updated. If I create a table by importing a spreadsheet, and then convert one of those fields to a lookup field, I find that it deletes the date that was already in that field. How do I prevent this from happening?

For upcoming training course dates see: Pricing & availability

Reply Thu 24 May 2007, 14:41Trainer Richard said...

RE: Look up fields/imported data

Sinead,

You could create the lookup at the form level, rather than at the table level. That way the underlying table would remain the same as imported and the form could modify the data where required.

Regards

Richard

Reply Thu 19 Jul 2007, 13:11Delegate Sin said...

RE: Look up fields/imported data

At the risk of sounding obtuse - do you mean typing in the data from a list of options at form level, or referencing a previously created lookup table at form level?

Reply Thu 26 Jul 2007, 16:03Trainer Richard said...

RE: Look up fields/imported data

Hi

Both your options above sound like they would work.
Perhaps it would help to know why you are creating a lookup for a date field? What are you looking up?

Thanks

Richard

Reply Fri 27 Jul 2007, 09:53Delegate Sin said...

RE: Look up fields/imported data

My apologies, I've reread my initial query and I was referring to deleting the data not the date.

Reply Mon 30 Jul 2007, 11:34Trainer Richard said...

RE: Look up fields/imported data

ok , thanks.

So you are importing a speadsheet into a table, and then creating a lookup on that field. My thought is that you are using the LOOKUP WIZARD from the data types. By using this the data is deleted at the last step to maintain the data integrity.

So, here is the workaround.

Instead of using the data type, go to the field properties area, and click on the LOOKUP tab.

1. Choose combo box
2. Row Source type will appear and be TABLE / QUERY
3. ROW SOURCE - click on the ....'s next to the arrow.
4. This will open a query window, which you can choose the table and field that you want to lookup.
5. Once the query is built, simply close the query using the X at the top left of the query window, and click YES on the message box
6. This is add an SQL statement like "Select [field] from [table].
7. The last step, if required, is to set the last field in the LOOKUP properties area "LIMIT TO LIST" to either YES or NO, depending on your requirements.

Once this is all done, then you can change to the table view, and save the table.
This should get you around the issue.

Let me know how you do.

Regards

Richard

Access articles

· Making The Transition To Access - Why You Might Need It
· How To Create Combo Lookups In Microsoft Access
· How To Make Access 2010 Databases Compatible With Access 2007
· Insert A Real-Time Clock In An Access Form
· Microsoft Access Training Unleashes the Power of the Pivot Table

Access tip:

Create a custom Close button on a Form

By default, Forms that you create in Access have a full set of control buttons, that is, a title bar with Minimize, Maximize and Close buttons in the top right-hand corner and a Control Menu in the top left-hand corner.

Sometimes Access needs to follow certain procedures or events as users close a Form (such as automatically opening another Form perhaps). So it is often a good habit to create a custom Close button on a Form for everyone to use, rather than the standard closing cross.

To create a custom Close button:

1) In Form Design View, draw a Command Button onto the Form.

2) Use the Command Button Wizard to select Form Operations under Categories, and Close Form under Actions.

3) Choose Finish.

Access then creates the button and code to close the form for you. You can add any extra code that needs to run as the Form closes to the same Close procedure.

However, the creation of a custom Close button is pointless if the closing X in the top right-hand corner of the Form remains active. To make the closing X inactive, open the Form Properties box then on the Format tab set the Close Button setting to No. This will grey out the closing X leaving it visible, but inactive.

View all Access hints and tips