changing Default Values

[Paul Simon]

I think this question should be straight forward, but I'm surprised I
can't find a similar question on this newsgroup.

I have a table which has three fields in it that have Default Values.

I'm going to add data to the table by appending from another table (by
using an Append query) NOT by entering it on a Form.

The contents of the other table change regularly as its linked to an Excel
spreadsheet, and I will be running the append query many times.

Hope the above is clear.

My question is;

I need to change the Default Values of the three fields after every 5 or 6
appends. I could just change the Default Values in Table Design but is
there an easier way. I'm going to be doing this appending every day, so I
want the process to be as easy and quick as possible.

Thanks in advance for any replies.

Paul

If my question's not clear, please let me know.

 

[Tim Ferguson]

(e-mail address removed)(Paul Simon) wrote in

I need to change the Default Values of the three fields after every 5
or 6 appends. I could just change the Default Values in Table Design
but is there an easier way. I'm going to be doing this appending every
day, so I want the process to be as easy and quick as possible.

I'm guessing this is something to do with labelling the records with
ImportedDate and DataSourceID etc.

The neatest way to do this is to put them in as part of the query:

INSERT INTO MyTable
( FieldOne, FieldTwo, FieldThree,
ImportedDate, DataSourceID
)
SELECT FirstField, SecondField, ThirdField,
#2004-04-03# AS TodaysDate,
"Alabama" As SourceOfThisStuff
FROM SomeImportSource
WHERE FourthField = FALSE;

Of course, you'd use parameters to put in the specific stuff, and the
aliases are not strictly required but help to make sense of what is going
on.

Hope that helps


Tim F

 

[Paul Simon]

(e-mail address removed)(Paul Simon) wrote in

I'm guessing this is something to do with labelling the records with
ImportedDate and DataSourceID etc.

The neatest way to do this is to put them in as part of the query:

INSERT INTO MyTable
( FieldOne, FieldTwo, FieldThree,
ImportedDate, DataSourceID
)
SELECT FirstField, SecondField, ThirdField,
#2004-04-03# AS TodaysDate,
"Alabama" As SourceOfThisStuff
FROM SomeImportSource
WHERE FourthField = FALSE;

Of course, you'd use parameters to put in the specific stuff, and the
aliases are not strictly required but help to make sense of what is going
on.

Hope that helps

Tim F

Hey Tim, Thanks.

So what you're saying is, I should re-think why I want to change the 3
Default Values, and that doing so might not be the best way of trying to
achieve my aim. Can you confirm that's what you mean.

You wrote;

I'm guessing this is something to do with labelling the records with
ImportedDate and DataSourceID etc.

If I understand you correctly, I think you're right. I want to add certain
standing data (the fields are Date(not today), Course, Going) that applies
to all records being appended, so I thought the simplest way (I don't know
VBA)was to change the Default Values for each of these fields at the
beginning of the session.

I should point out that the Default Values will then apply for up to 8
appends, which is why I thought it was worthwhile doing it this way. I am
also using an Update Query to add further standing data that only applies
to the current data append.

Anyway, if you still think what you suggested earlier is the best way, can
you explain what you've written to someone not familiar with VBA (if
that's what it is).

 

[Tim Ferguson]

(e-mail address removed)(Paul Simon) wrote in

So what you're saying is, I should re-think why I want to change the 3
Default Values, and that doing so might not be the best way of trying
to achieve my aim. Can you confirm that's what you mean.

Kind of: although Default Values can do the trick, I think you'd get more
control by specifying the values in the import query. As a practical point,
Default Values can be manipulated in Access but they are a b* unless you
are using ADPs.

If I understand you correctly, I think you're right. I want to add
certain standing data (the fields are Date(not today), Course, Going)

As an aside, "Date" is a rotten name for a field name, becuase it's a
reserved word in VBA and in SQL, and you are likely to trip over into some
really hard-to-track bugs if you make a small syntax errror. Use something
descriptive like RaceDate etc.

that applies to all records being appended, so I thought the simplest
way (I don't know VBA)was to change the Default Values for each of
these fields at the beginning of the session.

Yes, that's roughly what I guessed.

Anyway, if you still think what you suggested earlier is the best way,
can you explain what you've written to someone not familiar with VBA
(if that's what it is).

You haven't really specified how you are doing this import, but for example
if you are using the query grid, you can just enter some new columns like
this in the top row: this will create a column in the query with the given
value in every row.

RaceDate: 01/04/2003

You make two more columns for Course and Going.

To make it a bit more friendly, you can use a parameter like this. From the
Query | Parameters menu, enter a line like

[Enter Race Date]..... DateTime

and then change the column you did just above to

RaceDate: [Enter Race Date]

Now when you run the query, Access will give a dialog to type the date
into. To make it properly automatic and/ or user-friendly you can wrap the
whole thing in a bit of VBA to work out the Race Date, Going etc.

Hope that helps


Tim F