Field Population (1 Viewer)

NewfieSarah · Nov 23, 2005

Hi all, could help me with a problem. I have a form that displays only the records for a specific field. in my table I have many records so I have narrowed the records I want to display by displaying only the records with the same pin number. So if Pin number 2550 has 10 records then on my form there will be 10 records. Now I would like to add a record to make it 11 so I would like to have my fields populated from the information that I already narrowed. so the user doesnt have to enter it again. IE. my phone number, my name, and the current date doesnt change so I would like to be able to have that data appear when the button is clicked. any help would be great. So Any code Ideas would be great!

Thanks

Jerry Stoner · Nov 23, 2005

Use a dynamic default value

This will make the default value of a field the value of the record displayed(assume record 10 in your case)

If Not IsNull(Me.ListCodeID) Then
Me.Operatorid.DefaultValue = "='" & Me.Operatorid& "'"
'For text fields 'Me.Operatorid.DefaultValue = "='" & Me.OperatorID & "'"
'For date fields 'Me.Operatorid.DefaultValue = "=#" & Me.Operatorid & "#"
'For number fields 'Me.Operatorid.DefaultValue = "=" & Me.Operatorid

NewfieSarah · Nov 24, 2005

That code look okay, I will give it a try but can you explain it a bit more. Like
If Not IsNull(Me.ListCodeID) Then- what is not? and listCodeID
Me.Operatorid.DefaultValue = "='" & Me.Operatorid& "'"- Operatorid is my control name of my property correct? the name of the field on the form?? what is the =Me.Operatorid ? the field again or the table refrence? What is the "'" and the "#" for??
How does it know what the defaule value is??
Thanks

Jerry Stoner · Nov 27, 2005

Yes use the control name of the property.
# is used to denote a date
"'" inserts a quote to give the syntax =" & Me.OperatorID & " or as an end result = Me.Operator.ID

The code is taking what is currently in the field and making it the default value so the default changes each time you add data to the field - kind of a sticky data entry

- BTW use the code in the after update of the field

NewfieSarah · Nov 28, 2005

Hey Thanks for the info. owever I have tryed it in several different places afterupdate- form- afterupdate- field- add button- Nothing seem to be working. Maybe there is something that I am missing. Thanks

neileg · Nov 28, 2005

Sounds like you're duplicating data. Are you properly normalised?

NewfieSarah · Nov 28, 2005

yeah i am normalised!

NewfieSarah · Nov 28, 2005

oh I dont think i mentioned that I need the fields to populate when I add a new record. So is the code has to be in the add button somehow to make it appear when i click on the add! Thanks

neileg · Nov 28, 2005

NewfieSarah said:
yeah i am normalised!

How come you need to store the data associated with the PIN, again? Why can't you just store the PIN?

NewfieSarah · Nov 28, 2005

I Dont need to store data into pin, I need to use the data related to pin. When I add a new record. It will be using the Same Pin, Current Date, the same data, owner, phone number, since the same user only has one pin all the data realted to that will be automaticlly filled in so the user only needs to enter new data.

NewfieSarah · Nov 28, 2005

I Dont need to store data into pin, I need to use the data related to pin. When I add a new record. It will be using the Same Pin, Current Date, the same data, owner, phone number, since the same user only has one pin all the data realted to that will be automaticlly filled in so the user only needs to enter new data.

supercharge · Nov 28, 2005

This can be done with a simple copy-and-paste.

Let's say that all fields from the current record will be kept except "NewField" textbox when creating a new record. Place the following code into your AddNew_OnClick sub (assuming you click on a button to add new record).

Code:

DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.GoToRecord , , acNewRec
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdPaste

' Clear field for new value
NewField.value = ""

Pat Hartman · Nov 28, 2005

yeah i am normalised!

Not if you are duplicating all that data.

the same data, owner, phone number, since the same user only has one pin all the data realted to that will be automaticlly filled in so the user only needs to enter new data.

This data would be automatically filled without duplication by the simple expedient of basing the form on a query that joins the two tables on PIN.

NewfieSarah · Nov 28, 2005

hey supercharge thanks for the input however I tryed it and never got anything, just a blank record. I dont understand why! Is it becuase of the way my data is pulled from my table I wonder??

neileg · Nov 29, 2005

Pat, looks like a loosing battle, here!

NewfieSarah · Nov 29, 2005

Excuse me and why would you say that neileg??

neileg · Nov 29, 2005

Because you appear to be breaking the rules of database normalisation in storing the same data more than once. The data you seem to be duplicating is the personal data associated with the PIN. With a relational database there's no need to duplicate data, all you do is join the tables holding the related data with the unique identifier (the PIN).

Now I can't be 100% certain that your have un-normalised data, but it certainly seems that way. There are some circumstances where you might store apparent duplicates, where the associated data is subject to change for instance, like product prices, but that doesn't appear to be the case here.

I appologise if this is all obvious to you and you are satsified that your design is fully normalised. When I started posting in these forums, I though the concept was a load of rubbish! Hard practical experience has shown me that I was wrong.

Bat17 · Nov 29, 2005

IE. my phone number, my name, and the current date doesnt change

But if your phone number does change, do you want it to change in all of the past records or would the be expected to keep the old number still?
This is where Normalisation comes in, the phone number is part of 'you' so once 'you' are added the phone number will just tag along

Having said that there are times when you want to have fields that are essentialy the same each time. This is one of the first bits of code I wrote, I call it from a hot key when I start a new record that needs the same first fields.
I use the hot key as the various forms I use have the first few fields in common. Not good code, but it works for me.

Code:

Sub Up_Date()
Dim This_Form_Name As String
Static pc As String
Static bat As String
Static pn As String
Static v As String

On Error GoTo update_error_trap
This_Form_Name = Screen.ActiveForm.Name
DoCmd.GoToRecord acForm, This_Form_Name, acLast
 pc = Screen.ActiveForm![Product Code]
 pn = Screen.ActiveForm![Product Number]
 bat = Screen.ActiveForm![Batch Number]
 DoCmd.GoToRecord acForm, This_Form_Name, acNewRec
 Screen.ActiveForm![Product Code] = pc
 Screen.ActiveForm![Product Number] = pn
 Screen.ActiveForm![Batch Number] = bat
 Screen.ActiveForm![Roll Number].SetFocus
update_error_trap:
End Sub

HTH

Peter

supercharge · Nov 29, 2005

NewfieSarah said:
hey supercharge thanks for the input however I tryed it and never got anything, just a blank record. I dont understand why! Is it becuase of the way my data is pulled from my table I wonder??

It shouldn't matter how your form pulls data from a table.
When you first open your form, is it already showing data? It must show data in order to be copied over to the next record.

Try the sample attached here.

NewfieSarah · Nov 29, 2005

hey supercharge, thanks for the sample. So that looks like it works okay! However I was wondeirng if I can select specific fields instead of the whole record. I aslo guess I can add my own code after the cleared text box to create my own value. Right. IE- Parcle Id- current date+ 1 instead of blank for the user to type in. Yes there is already data showing when my for opens. Thanks

Field Population (1 Viewer)

Registered User.

Jerry Stoner

Guest

Registered User.

Jerry Stoner

Guest

Registered User.

AWF VIP

Registered User.

Registered User.

AWF VIP

Registered User.

Registered User.

Registered User.

Super Moderator

Registered User.

AWF VIP

Registered User.

AWF VIP

Registered User.

Registered User.

Attachments

Registered User.

Similar threads

Users who are viewing this thread