Simple Data Copying Question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form based on a query that filters the data through a [Choose a
Town] prompt. The "Town" text box, indicating the focus town, is located in
the Form Header, with the record information under the detail section. When I
create a new record at the bottom of the form, I need the name of the town at
the top of the page to copy into the table with the new record information.
I'm assuming/hoping there is a simple way of accomplishing this (4 line
VBA?). Thanks

- Mat
 
Hi, Mat.
When I
create a new record at the bottom of the form, I need the name of the town at
the top of the page to copy into the table with the new record information.
Click to expand...

There are several ways to do this, but if you don't want to alter your form,
you can accomplish it entirely with a few lines of VBA code in your form's
OnCurrent( ) event. For example:

Private Sub Form_Current()

On Error GoTo ErrHandler

If (Me.NewRecord) Then
Me!txtTown.Value = Me!cboTown.Column(1)
End If

Exit Sub

ErrHandler:

MsgBox "Error in Form_Current( ) in" & vbCrLf & _
Me.Name & " form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear

End Sub

.. . . where txtTown is the name of the text box bound to the field for Town,
cboTown is the name of the combo box displaying the name of the town, and 1
represents the 2nd column of the combo box's Row Source, which, in this
example, is the display column. You may need to alter this number to suit
your situation.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.


bironmx said:
I have a form based on a query that filters the data through a [Choose a
Town] prompt. The "Town" text box, indicating the focus town, is located in
the Form Header, with the record information under the detail section. When I
create a new record at the bottom of the form, I need the name of the town at
the top of the page to copy into the table with the new record information.
I'm assuming/hoping there is a simple way of accomplishing this (4 line
VBA?). Thanks

- Mat
Click to expand...
 
Sorry for taking so long to reply to what you suggested.

I tried inputting the code that you wrote for me but this is the problem
that I'm having now. I'll try to give you a better explanation.

The record I'm creating is in a Continuous Form that is similar looking to a
datasheet view. The query asks for the town name and then I use that name in
the heading of the form (just a plain text box). The town is not re-displayed
in a text box in the detail section. I'm not sure if this information holds
any baring but the problem I'm having with your code is that when I start to
enter information into a new record, the code "deletes" the name of the town
from the form header and leaves the town name blank in my table. Though, if I
type the name of the town back into the header it appears in the new record.

I'm sorry if this doesn't help you understand my problem.

- Mat

'69 Camaro said:
Hi, Mat.
When I
create a new record at the bottom of the form, I need the name of the town at
the top of the page to copy into the table with the new record information.
Click to expand...

There are several ways to do this, but if you don't want to alter your form,
you can accomplish it entirely with a few lines of VBA code in your form's
OnCurrent( ) event. For example:

Private Sub Form_Current()

On Error GoTo ErrHandler

If (Me.NewRecord) Then
Me!txtTown.Value = Me!cboTown.Column(1)
End If

Exit Sub

ErrHandler:

MsgBox "Error in Form_Current( ) in" & vbCrLf & _
Me.Name & " form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear

End Sub

. . . where txtTown is the name of the text box bound to the field for Town,
cboTown is the name of the combo box displaying the name of the town, and 1
represents the 2nd column of the combo box's Row Source, which, in this
example, is the display column. You may need to alter this number to suit
your situation.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.


bironmx said:
I have a form based on a query that filters the data through a [Choose a
Town] prompt. The "Town" text box, indicating the focus town, is located in
the Form Header, with the record information under the detail section. When I
create a new record at the bottom of the form, I need the name of the town at
the top of the page to copy into the table with the new record information.
I'm assuming/hoping there is a simple way of accomplishing this (4 line
VBA?). Thanks

- Mat
Click to expand...
Click to expand...
 
Back
Top