Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations sizbut on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

populating default values in tables 1

Status
Not open for further replies.

uscitizen

Technical User
Jan 17, 2003
672
US
hi -

i know that you can link a database with others and so forth and i got to wondering. i have created a generic database which has a myriad of tables most of which will have the same default values in two controls: "Protocol" and "Title". i would like to be able to minimize the amount of time spent manually keying in the same two values for "Protocol" and "Title" within each database ["Protocol" and "Title" will always have the same values across tables w/in each database, but across databases they won't, e.g. if this too murky:

database_1 database_2
"Protocol" = 00-12-134 "Protocol" = 34-168-038
"Title" = 'Study of Xyz' "Title" = 'Study of aBx'

i got to wondering if it would be possible to create a database having a table with two columns, called "Protocol" and "Title" which could be used to link to the 'Default Value' of the "Protocol" and "Title" fields in each table.

OR -

since, every table in the database has a corrsponding form with the "Protocol" and "Title" controls atop it in the header, if there's some theoretical way to link the two controls on the form with a lookup table in an external database which would require the user to know that database's password...the idea being in this instance to restric the user from changing the values of "Protocol" and "Title" once they'd been entered by an 'Admin' type (someone w/ the password required to enter the external database and choose each's respective value from the combobox of offerings).

i think the first 'idea' is a little unworkable in retrospect and that possibly the 2nd might 'fly'.

any points to ponder?
 
You can set a text box's default value in the On Open event of the form.

Me.txtProtocol.DefaultValue = DLookup("Protocol", "tblDefaults")

I hesitate to ask but is there a good reason why you are storing the same values in multiple tables?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.
 
first, an attempt at a paraphrase....

in every form where i want the value(s) of the two controls, i.e. "Protocol" and "Title" to be the same for all records, i would implement an "On Open" event which read either

Me.Protocol.DefaultValue = DLookup("Protocol","tblDefaults")

or

Me.Title.DefaultValue = DLookup("Title","tblDefaults")

respectively.

i like this approach, if i understand it correctly, because it would let me populate the "tblDefaults" table with the values for each field rather than having to enter them twice in each table underlying every form displaying them!


the reason for this is sort of cosmetic: the fields appear in the headers of each form and act to provide a sort of title to it.

 
Does the "#Name?" (w/o the quotations) error showing up in a control on a form point to the solution.

Here's what I've done since returning to work this morning.
I took your advise and created a table called "tblDefaults" which contains two controls "ProtocolID" and "ProtocolTitle", and having just one record with both the number field and text field entered.

In the 'OnOpen' event of the form, I added (to the pre-existing "DoCmd.Maximize" command your suggestions and have pasted the resulting code below:

Private Sub Form_Open(Cancel As Integer)
DoCmd.Maximize
Me.Protocol_ID.DefaultValue = DLookup("ProtocolID", "tblDefaults")
Me.Protocol_Title.DefaultValue = DLookup("ProtocolTitle", "tblDefaults")
End Sub

bottom line is when i open a new record in the form to which this has been added, the number of "Protocol_ID" appears in its control, but "#Name?" (without the quotations) appears in the "Protocol_Title" control??!

does this have an explanation?
 
You might need to add delimiters for text defaults
Me.Protocol_Title.DefaultValue = """" & DLookup("ProtocolTitle", "tblDefaults") & """"
Are you actually placing these values in bound text boxes or would you be better off setting the caption of a label or just the Value of a text box?


Duane
MS Access MVP
Find out how to get great answers faq219-2884.
 
Yes, voila, I think you've got it this time:

Private Sub Form_Open(Cancel As Integer)
DoCmd.Maximize
Me.Protocol_ID.DefaultValue = DLookup("ProtocolID", "tblDefaults")
Me.Protocol_Title.DefaultValue = """" & DLookup("ProtocolTitle", "tblDefaults") & """"
End Sub

now appearing in the 'On Open' event property of the form remove the error code.

regarding your question:

"Are you actually placing these values in bound text boxes or would you be better off setting the caption of a label or just the Value of a text box?" -- they are going into bound text boxes. Although I indicated that they have 'cosmetic value' at the present time, as they serve to 'title' each form, in the event tables are exported from a database and combined with their twin/counterpart tables which appear in other databases used by folks for other protocols, I wanted to be able to identify the origins of records in the resulting synthesized table (since identical patient numbers might appear in more than one database). there may be more 'elegant' approaches but this occurred to me and i think i'll stick with it.

i'd like to be able to understand why the appending's necessary to get the text field to fly, but it's not a biggee, you get a red star anyways :)


 
To understand why the """" is needed, just add a default value to any text box bound to a text field. You will notice that if you don't put quotes around the value, Access will add them. Add a default value to a text box bound to a number and there are no quotes.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.
 
I had noticed that when I added defaults in the table's properties, quotations were required.

Thx.
 
I thought this was finally put to bed, but I just discovered I have a table called "ECOG Evaluation" which does not have a data entry form, and, which, in addition to having one or two other fields, has the same two identifying fields ("Protocol_ID" and "Protocol_Title") needing the default values. I wondered if there's a way to populate the "ECOG Evaluation" fields when the user is opening the form "Registration".
 
You need to be a little more helpfull. You said "ECOG Evaluation ... does not have a data entry form".
1) How do values get entered or edited in the table?
2) What does this table have to do with a form named "registration"?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.
 
I was afraid you'd ask. It's a little complicated but let me try to explain the relevant parts:

"ECOG Evaluation" is involved in two forms where it provides the "Protocol_ID" and "Protocol_Title" information in the headers of each and where it's "Patient_Number" is the linking master field with the child sub-forms in each. The two forms we'll call "Target Lesions" and "Non-Target Lesions". Their respective sub-forms are called "Normalized: Review of Target Lesions subform" and "Lestion Evaluations". The data sources of the sub-forms are "Sum of Target Lesions" and "Lestions Evaluations" respectively. [Are you still following this?]

Getting back to the "Registration" form, here is some VBA code from two event properties' of "Registration"s...

Private Sub Form_BeforeUpdate(Cancel As Integer)
Call AuditTrail([Form])
' Changes made by Microsoft (09/18/03)
If Me.NewRecord = True Then
Addmode = 1
Else
Addmode = 0
End If
'End of changes made by (09/18/03)
End Sub


Private Sub Form_AfterUpdate()
Call acbLogUpdate("Registration", [Patient Number])
' Changes made (09/18/03)
If Addmode = 1 Then
' Added (9/18/03) to update data in Ecog evaluation table,
CurrentProject.Connection.Execute "INSERT INTO [ECOG Evaluation] ( [Patient Number] ) VALUES (" & Me.Patient_Number.Value & ")"
' Added (09/18/03) to update data in Ecog evaluation table, case
CurrentProject.Connection.Execute "INSERT INTO [Lesions Evaluations] ( Visit, [Patient Number] ) SELECT [Look_Visit].[Visit]," & Me.Patient_Number.Value & " FROM Look_Visit; "
CurrentProject.Connection.Execute "INSERT INTO [Sum of Target Lesions] ( Visit, [Patient Number] ) SELECT [Look_Visit].[Visit]," & Me.Patient_Number.Value & " FROM Look_Visit; "
End If
Addmode = 0
'End of changes made (09/18/03)
End Sub

and thanks to you, we added

Private Sub Form_Open(Cancel As Integer)
DoCmd.Maximize
Me.Protocol_ID.DefaultValue = DLookup("ProtocolID", "tblDefaults")
Me.Protocol_Title.DefaultValue = """" & DLookup("ProtocolTitle", "tblDefaults") & """"
End Sub

Before deciding to streamline the job of populating the default values of ID and Title, each table was manually hardcoded with the same two default values for ID and Title throughout the database. Now that that is not any longer the case, when you attempt to add a record to the "Registration" form (which the user is instructed is the first order of business when entering a new patient), you get a message from A2K about the fields for ID requiring a value because it's a required field. The literal quote of the message reads:

Run Time Error: very large number here

"The field 'ECOG Evaluation.ProtocolID' cannot contain a Null value because the Required property for this field is set to True. Enter a value in this field."

when you hit the 'Debug' button, the following line of VBA code is highlighted yellow:

CurrentProject.Connection.Execute "INSERT INTO [ECOG Evaluation] ( [Patient Number] ) VALUES (" & Me.Patient_Number.Value & ")"


This was not a problem in a slightly earlier copy of this database when I had as yet to remove the values for the defaults of these two fields from the ECOG Evaluation table, so I must assume that we need to pre-populate each with their respective value if the VBA code is going to do its thing.

In a nutshell :)
 
Can't your INSERT INTO sql just contain values for the required fields?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.
 
being a newbie (who, hopefully, will have completed a vba course by year's end), what would you suggest the code look like?
 
Dim strSQL as String
Dim lngProtID as Long
Dim strProtTitle as String
lngProtID= DLookup("ProtocolID", "tblDefaults")
strProtTitle=DLookup("ProtocolTitle", "tblDefaults")
strSQL = "INSERT INTO [ECOG Evaluation] ( [Patient Number], [ProtocolID], [ProtocolTitle] ) " & _
"VALUES (" & Me.Patient_Number.Value & ", " & lngProtID & ",""" & strProtTitle & """)"
CurrentProject.Connection.Execute strSQL


Duane
MS Access MVP
Find out how to get great answers faq219-2884.
 
hi,

we're almost there but for the 'syntax error' ms a2k claims to find with the state of the current code on the line with "CurrentProject.Connection.Execute strSQL" which it highlights (in yellow):

Private Sub Form_Open(Cancel As Integer)
DoCmd.Maximize
Me.Protocol_ID.DefaultValue = DLookup("ProtocolID", "tblDefaults")
Me.Protocol_Title.DefaultValue = """" & DLookup("ProtocolTitle", "tblDefaults") & """"
' Added 7 lines below by me (11/30/03)
Dim strSQL As String
Dim lngProtID As Long
Dim strProtTitle As String
lngProtID = DLookup("ProtocolID", "tblDefaults")
strProtTitle = DLookup("ProtocolTitle", "tblDefaults")
strSQL = "INSERT INTO [ECOG Evaluation] ( [Patient Number], [Protocol ID], [Protocol Title] ) " & _
"VALUES (" & Me.Patient_Number.Value & ", " & lngProtID & ",""" & strProtTitle & """)"
CurrentProject.Connection.Execute strSQL
End Sub

specifically, when you open the "Registration" form, you get a grey window from Microsoft VB with a 'Run time error' followed by a large negative number, below which appears the statement:

"Syntax error in INSERT INTO statement"

i confess to having added spaces to "ProtocolID" and "ProtocolTitle" after 'protocol' in your original code to conform with the field names in the 'ECOG Evaluation' table.
 
Why are you doing this in the On Open event of the form? I thought this was in the After Update event before.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.
 
Still the newbie, I guess, but I experimented with moving it to the AFter Update (after sending the above) and it seems to be holding water. Will have to take a longer look at it just to make certain. I'm hopeful it'll work though.

Many thanks :)
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top