Field default value in a form (2000)

Topic

New Reply

I have a table with three fields in each record: managed net outstandings, held net outstandings and Data Date. A form named “update outstandings” is used to populate these fields in the underlying table. What I would like to do is have “held net outstandings” default to the value of “managed net outstandings” as the data is entered in the form. I’d also like the “data date” field values to default to the first “data date” entry made, since all the records will be updated at the same time. I’ve tried playing with the “after update” control in “managed net outstandings” and “data date”, but I can’t seem to get it to work correctly (compile error, can’t find project or library). Any help would be greatly appreciated. Thanks

Reply | Quote

Replies

When you get the “Can’t find project or library” error, what part of your code is highlighted?

Reply | Quote

I’m just learning to code in Access, so I apologize if this is way off…

Anyway, the first thing I tried to tackle was the default for the “Data date” field.

Private Sub Last_Update_Date_AfterUpdate() <– This is highlighted when the compile error box pops up.

Update_Outstandings!Data_Date.DefaultValue = """" & Update_Outstandings!Data_Date.Value & """"

End Sub

I'm not sure where to begin to get the "net outstandings" to default to the "managed outstandings."

Reply | Quote

In the first place, remove Update_Outstandings! (twice). If you needed to refer to the form explicitly (which you don’t, since you’re on that form), it should have been Forms!Update_Outstandings!…, but you can omit it here.

See if the error goes away.

If not, what is Last_Update_Date? The line after it mentions Data_Date, so shouldn’t it be Data_Date? See what happens if youchange Last_Update_Date_afterUpdate to Data_Date_AfterUpdate.

Note for the future: you have used names for fields, controls and form with spaces in them. Although this is allowed, it is not to be recommended. It makes coding more difficult; as you can sse here, Access has replaced spaces by underscores. You can always set the caption property of fields to make labels display user-friendly text.

Reply | Quote

Yes, I realized I shouldn’t be using spaces long after I should have…

Anyway, I changed it to:

Private Sub Last_Update_Date_AfterUpdate()

Last_Update_Date.DefaultValue = “””” & Last_Update_Date.Value & “”””

End Sub

It now runs without error, but nothing happens. Shouldn’t this populate the remaining Last_Update_Date fields in the table with the same value?

Reply | Quote

Open your form in design view.
Select the “Last Update Date” control.
Activate the Properties window, Events tab
Click in the AfterUpdate event.
If it hasn’t been selected, select Event Procedure.
Click the builder button (the three dots …)
You should be taken to your procedure.
Switch back to Access, and save the form.
Test if AfterUpdate now works.

Reply | Quote

No Joy

Is there a setting somewhere in the table or form that would prevent this from working? Also, I have the date field configured as MM/YYYY.

BTW, thank you for your patience and help.

Reply | Quote

Try the following:

Repeat the steps from my previous reply until you are in the Visual Basic Editor (or return there if it is still open).
Click in the margin to the left of the line Private Sub Last_Update_Date_AfterUpdate()
The line should be highlighted in brown, and should be marked by a brown bullet in the left margin. This is called a breakpoint.
Switch back to Access, and open the form. Change the date and press Tab or Enter.
You should be taken to the Visual Basic Editor, with execution of the event procedure paused at the breakpoint.
If that doesn’t happen, something must have gone wrong when you followed the steps from my previous post.
If it does, single step through the code by pressing F8 repeatedly.
Does the highlighted yellow line move down, and disappear when you’ve come to the end of the procedure?

Reply | Quote

Yes, the highlighted yellow line moves down through the steps and disappears at the end.

Reply | Quote

I’m afraid I don’t understand why it doesn’t work. Let’s try something different.

In Microsoft Knowledge Base Article 210236, Microsoft describes a method of filling one or more fields in a new record with values from the previous record. You can also download a sample database with a form that demonstrates this method. I hope that you can apply it to your database.

I hesitate to ask it, but if you wish, you can attach a stripped down, sanitized, compacted and zipped version of your database to a post. That way, other Loungers can try to pin down the problem. Remove everything that is not needed to demonstrate the problem. The zip file should be less than 100 KB.

Reply | Quote

I stripped it down to the table and the form. If someone could help me get the net outstanding value to default to the value in managed outstandings and get my date problem fixed, I would be very grateful, as I am already for the help.

Thanks,

Brett

Reply | Quote

It works! But what in heavens name is your purpose? The form doesn’t allow adding a new record, so you’ll never see the effect of setting the default value!

I have set Allow Additions to Yes, and added code to set the default value (and optionally the value in the current record) of Held Net Outstandings to the value of Managed Net Outstandings.

Reply | Quote

Trust me, there is another form for this table that allows for records (vendors and vendor numbers) to be added. This form is for only updating the outstandings $$. There is also an append query that moves the old outstanding values to a different table when you open this form.

Anyway, I really appreciate the help. I’ll let you know tomorrow if I was able to get it to work.

Regards,

Brett

Reply | Quote

Good news and bad news. Good news: The net outstanding field defaults to the managed outstanding without a problem. Bad news: Bad communication on my behalf. What I’m trying to do is when the date is changed in the first record, I want it to change the date field for the rest of the existing records. For example, all of the records have a date of 4/2003. When I update the first record to 5/2003, I want the rest of the records to default to that value as well. Is this even possible?

Thanks for all of your help.

Brett

Reply | Quote

Defaults only apply to new records. Do you mean you want the rest of the records *updated* to the new date? *All* existing records?

Reply | Quote

Yes, that is a much better description of what I’m trying to accomplish.

Reply | Quote

Something like this will change the date when it is changed in any record, not just the first one:

Private Sub Last_Update_Date_AfterUpdate()
CurrentDb.Execute “UPDATE Vendors SET [Data Date] = #” & Format(Last_Update_Date, “mm/dd/yyyy”) & “# ”
End Sub

But, as you will find, this causes a conflict. You should only set the date for other records. For this, you need a unique key, for instance an AutoNumber field VendorID. Then you can use

CurrentDb.Execute “UPDATE Vendors SET [Data Date] = #” & Format(Last_Update_Date, “mm/dd/yyyy”) & “# ” & _
” WHERE VendorID ” & VendorID

Reply | Quote

The vendor_number is a unique field, so I tried this:

CurrentDb.Execute “UPDATE Vendor_Number SET [Data Date] = #” & Format(Last_Update_Date, “mm/dd/yyyy”) & “# ” & _
” WHERE Vendor_Number ” & Vendor_Number

Of course it failed since I’ve got something named incorrectly. I get “can find project or library” error and it highlights “Format” no matter what I try. So where and how do I put the proper name in? I

Reply | Quote

In the Visual Basic Editor, select Tools/References…
Uncheck any reference starting with MISSING, and also uncheck the Microsoft DAO Compatibility Library (or something like that), then click OK. Switch to Access and try the form.

Reply | Quote

I changed the code to read:

CurrentDb.Execute “UPDATE Vendors SET [Data Date] = #” & Format(Last_Update_Date, “mm/dd/yyyy”) & “# ” & _
” WHERE Vendor_Number ” & Vendor_Number

But now I get the error: runtime error 3061 too few parameters. expected 2. Argh. I’m sorry this is so painful.

Reply | Quote

Check very carefully if the table name and field names are spelled exactly as they are in the table. The field name for vendor number may very well be Vendor Number, in which case you must use

” WHERE [Vendor Number] ” & Vendor_Number

Reply | Quote

I tried:

CurrentDb.Execute “UPDATE Vendors SET [Data Date] = #” & Format(Last_Update_Date, “mm/dd/yyyy”) & “# ” & _
” WHERE [Vendor Number] ” & [Vendor Number]

and

CurrentDb.Execute “UPDATE Vendors SET [Data Date] = #” & Format(Last_Update_Date, “mm/dd/yyyy”) & “# ” & _
” WHERE [Vendor Number] ” & Vendor_Number

Now I get Runtime error 3061 too few parameters. Expected 1.

Reply | Quote

Well, that’s one less than before, so you’re halfway there

On looking back at your database, I notice that Vendor Number is not a number, but text. (Long live consistency). Should have remembered that. That means that the value must be enclosed in quotes. Try

CurrentDb.Execute “UPDATE Vendors SET [Data Date] = #” & Format(Last_Update_Date, “mm/dd/yyyy”) & “# ” & _
” WHERE [Vendor Number] ” & Chr(34) & Vendor_Number & Chr(34)

ASCII/ANSI character 34 is the double quote “.

Reply | Quote

That did it! But from now on I’m going to tell people that doing repetitive input is good for them.

Thanks again,

Brett

Reply | Quote