Default Value on a Form based on a Query

[ErikRP]

Hopefully this is a simple one for a Friday...

I have a query as follows:

SELECT [ENTER QUARTER (Q1, Q2, Q3 or Q4)] AS [Working Quarter], *
FROM [BPO London]
WHERE [Quarter] = [ENTER QUARTER (Q1, Q2, Q3 or Q4)];

I have a form that is based on this query.

When the form opens it displays information that the user can add to. I want the field [Quarter] to have the default value of [Working Quarter].

For example, if I'm working in Q2, I want any new records that I add to automatically default to 'Q2' rather than having to type it out each time.

I've tried such things as =[Working Quarter] or = [Query LON]![Working Quarter] but it doesn't work. Either I get #Error or it's blank.

Any thoughts?

[This message has been edited by ErikRP (edited 04-05-2002).]

 

[David R]

Default: DatePart("q",Date())

If I understand your question correctly, that should give you what you want.

 

[ErikRP]

Hi David,

It would give me the current date, but unfortunately users will want to view (and edit) previous quarters, which I don't think your solution will help.

Isn't it possible to simply have the input value [WORKING QUARTER] as the default value? Well, it appears that it's not possible, so I guess the question is why not?

 

[David R]

Errr, it should be possible. I guess my counter question is: where does [WORKING QUARTER] come from? Is this a parameter they enter when they open the form? Is it selected from a combo box on the form itself? There are a couple of different ways to tackle this. Let me dredge up some old topics on this:
http://www.access-programmers.co.uk/ubb/Forum4/HTML/006195.html
http://www.access-programmers.co.uk/ubb/Forum4/HTML/005759.html
http://www.access-programmers.co.uk/ubb/Forum4/HTML/006451.html
Good luck,
David R

[This message has been edited by David R (edited 04-05-2002).]

 

[ErikRP]

Hi David,

Thanks for the links. The second example appears to be the most similar situation. I'll look at it a bit more closely in a few minutes...

To answer your "counter-question", the value [WORKING QUARTER] is entered by the user:

SELECT [ENTER QUARTER (Q1, Q2, Q3 or Q4)] AS [Working Quarter]

If I create a text box in the form with the value [WORKING QUARTER], I see the value that I input. I have not been able to get that value as the default value in another text box. I don't want to just show the value that I am inputting, but I want that value to populate a field on the original table.

 

[David R]

Vaporcode here, but did you try something like:

Private Sub SomeField_GotFocus()
    Me.BoundQuarterlyField = [WORKING QUARTER]
End Sub

But why are you storing the working quarter with every record? Shouldn't that already be there, so that you can query it?? Your question seems to have changed dramatically from the start and I'm not sure I'm giving accurate advice any longer.

Good luck,
David R

 

[ErikRP]

I don't think I've changed things, but maybe I wasn't clear. I'll try again.

Here is my table [BPO LONDON](simplified): BPO, QUARTER, OFFICE, ADJUSTER.

Here is my query:

SELECT [ENTER QUARTER (Q1, Q2, Q3 or Q4)] AS [Working Quarter]
FROM [BPO London]
WHERE [Quarter] = [ENTER QUARTER (Q1, Q2, Q3 or Q4)];

The form is just a simple form that is based on the Query. I want the user to enter in the quarter (e.g. Q1) for which they want to add additional records. They also need to see the records for that quarter already entered. So, the form opens, runs the query and up pops:

ENTER QUARTER (Q1, Q2, Q3 or Q4)

They enter 'Q1' for example and the form produces all of the records from Q1. Now, when the user hits the 'new record' button, I want 'Q1' to automatically fill the QUARTER field. Ideally I should just be able to have the default value of QUARTER be =[WORKING QUARTER] but that doesn't want to work.

I'm a complete novice to any sort of code, but if it needs to be code, so be it, but I'd appreciate a simple approach.

Thanks for your help, DavidR!

 

[David R]

Ahhhh, gotcha. There are two (basic) ways to get some value from the previous record.

1) Non-code: Use ctrl-' to get the same value from the previous record. This requires keystrokes on the part of your users, and awareness that they can do this (not to mention making them remember to do so).
2) (code) Look in the archives for the 'PrevRecVal' function. Rich has posted about it repeatedly, and it's a code snippet that you may find useful for what you want.
3) Also you can search the Forms archives for 'previous record value' and you'll get several alternate suggestions.

HTH,
David R

 

[ErikRP]

Thanks for the help, DavidR! I found this from Jack Cowley and it seems to be 99% of what I want. The only thing it won't do is populate the first field after I run the query - I have to manually type the first line - but for subsequent records it does the auto-populate. Works great!

Thanks again David - and Jack!

For those who are interested (from Jack Cowley):

In the After Update event of the UserID field put code like this:
Me![UserID].Tag = Me![UserID].Value

In the On Enter Event of the same field put code like this:

If Not Me.NewRecord Then Exit Sub
If Not (IsNull(Me![UserID].Tag) Or Me![UserID].Tag = "") Then
Me![UserID].Value = Me![UserId].Tag
End If

The UserID will not carry over into your next session with the form but will work as long as the form is open....