Automaticly Setting Default Value

[swicklund]

I have a button set up to open a FORM, and only pull specific data from a table based on the value in a TEXT BOX (this is functioning correctly). I would also like to automatically set the DEFAULT VALUE Property for the TEXT BOX so every record that is added will have the correct value (I am not able to get this working).



If I make a different form for each set of data pulled from the table, I could make this work easily enough. I would just like to make the database a little less bulky.

Bottom Line: Can I use VB Code to automatically set the DEFAULT VALUE on the DATA TAB of the PROPERTY SHEET for a specific TEXT BOX?

Thank you,

swicklund

Access 2010 User

[Missinglinq]

Not really clear about where you're trying to do this, and thus not sure about how the Value of this Textbox is being made available, but the generic VBA syntax for this would be

Me.ControlName.DefaultValue = "Whatever"

Linq ;0)>

[swicklund]

I have entered the code you suggested see below:

Private Sub Command68_Click()

DoCmd.OpenForm "3_TEST_FRM", , , "Component_Name = 'Test Component 4'"
Me.Text76.DefaultValue = "Test Component 4"

End Sub

The following Error Popped Up:


I them modified the code slightly (changing the Me. to Me!):

Private Sub Command68_Click()

DoCmd.OpenForm "3_TEST_FRM", , , "Component_Name = 'Test Component 4'"
Me!Text76.DefaultValue = "Test Component 4"

End Sub

The form opened, but then I got the following error:


I have triple checked, and the ControlName (Text Box with the Name: Text76) was typed in correctly.

Any thoughts?

swicklund
Access 2010

[Missinglinq]

As I said, you didn't state where you were trying to do this! In point of fact, you cannot add any Record in 3_TEST_FRM using your current method! You're using the Where clause of the DoCmd.OpenForm command which only allows you to go to that single Record. To do what you want you have to use OpenArgs to pass the Value and go to that particular Record.

On top of this, you're using

"Component_Name = 'Test Component 4'"

which indicates you only want to pull a Record whose Component_Name is Test Component 4. Is that your intent, or do you want the second Form's Record value be equal to that value on the current Record in the primary Form?

Linq ;0)>

[swicklund]

The purpose of the database that I am developing is to manage the maintenance records for various components (i.e. Valves, Pumps, Motors, etc.), and my plan is to store all of the data in one table (I had originally set it up where each component had its own Table, Form, and Report). As you can imaging, the size grew very fast (it was over 80 MB).

My purpose in using the DoCmd.OpenForm "3_TEST_FRM", , , "Component_Name = 'Test Component 4'" Command is to open a form that will show all the maintenance records for a given component (Test Component 4 in this case). The reason that I want to set the Default Value when I open the form, is so when the user adds maintenance items to the list, the Component Name will automatically be filled.

I have a stripped down version of my database as a reference, but it is still over 1.5MB and cannot be attached (I have tried compressing it, but this is as small as it gets).

As you can tell, I am not an experienced database developer, but am wondering if there is a way to store a text value (in this example "Test Component 4") in temporary location when I press the button to open the form, I could use this value to fill the Component_Name when I add records.

I appreciate your help!!

swicklund

[swicklund]

Problem Solved!!

I set up a Temporary Table that holds the "Value" that I want the DEFAULT VALUE to be set too (see code below):

Private Sub Command68_Click()
' Define Variables

Dim stTemp As String
Dim TempTable As String
Dim TempField As String
Dim dbs As DAO.Database
Dim rsVideos As DAO.Recordset

' Set Temporary Variable Names

TempTable = "Temp_TBL"
TempField = "TempField"

' Delete Current Temporary Table

DoCmd.DeleteObject acTable, TempTable

' Create New Temporary Table

Set dbs = CurrentDb
Set tbl = dbs.CreateTableDef(TempTable)
Set fld = tbl.CreateField(TempField, dbText, 255)
tbl.Fields.Append fld
dbs.TableDefs.Append tbl
dbs.TableDefs.Refresh

' Set Field Value

Set dbs = CurrentDb
Set rsVideos = dbs.OpenRecordset(TempTable)

rsVideos.AddNew
rsVideos(TempField).Value = "Test Component 4"
rsVideos.Update

' Open Form for Test Component 4 Only

DoCmd.OpenForm "3_TEST_FRM", , , "Component_Name = 'Test Component 4'"

End Sub

I then used DLOOKUP in the DEFAULT VALUE field to pull the information. It may not be the most elegant solution, but it does work.

Thank you for all of the help!

swicklund