stuffing a value into a form field

[Josh Sharpe]

Here's the problem: I have one main form that has a button to open up
another form. Once data entry on the second form is complete the user would
click a save button which I want to save the record to the table AND take
the new primary key value and stick it into the first from. So far I've
been using a macro(append query, then look-up query) to do this, but I can't
find a way to stuff that value into the main form.

Plz help,
-Josh

 

[Josh Sharpe]

I learned something there, but I dont think that that will work since the
primary key that I am looking to store isn't generated till the new record
is saved. Secondly the primary key field isn't even on the form. I was
going to retreive it from a search query(which I know how to do) Lastly, I
tried using an update query to update the formfield using the same thing you
just gave me, and it returns an error saying that the field on the first
form isn't updatable.

-Josh

 

[Joshua A. Booker]

Josh,

If you are using Access db table (not SQL) and the PK is an autonumber
field, the autonumber is generated before the record is saved. Access
inserts the record after the first letter is typed into any field on the
form. You can see this happen if you display the autonumber field in the
form. The same may be tru efor SQL, I don't know for sure.

Also, if the filed is included in the form's recordsource, you can refer to
it in VBA regardless if it's displayed on the form.

There are many reasons why a field may not be updateable so I can't help
there without further info.

HTH,
Josh

 

[Josh Sharpe]

The form is unbound, the field is unbound and I use a query to update with.
I'm not sure if it gets more simple than this. I'm also not sure what info
you need to help answer it. So let me know and I'll be glad to answer.
-Josh

 

[Joshua A. Booker]

Josh,

In fact, it does get more simple than that. I my opinion, bound forms and
controls are simpler than unbound for several reasons. One reason is that
you won't have to worry about using queries to insert or update records.
After all, that's what Access is designed to do for you. Unless you have a
good reason to use unbound forms, I'd suggest letting Access do the work for
you.

If the first form is unbound, you should not get the 'not updateable' error.
An unbound textbox (one having a blank controlsource property) is always
updateable and that error usually is referring to whether the form's
recordsource is updatable. An unbound form (blank recordsource property)
has no recordsource.

My suggestion is for bound forms and controls. Using SQL to lookup the ID
and again to update the field, then again the redisplay the values on the
first form is the only way to do it on an unbound form. Seems like a lot of
work. Especially when Access does it faster and more accurately by itself
if you simply use bound forms.

HTH,
Josh

 

[Josh Sharpe]

Okay, now I've quickly made two forms that are both bound. Added a button
to open up the second form and added the code you gave me to the onClick
event. It gives me this error:

*
Microsoft ACcess can't find the macro
'Forms!frmNewFaculty!AddressID=Me!AddressID.'

The macro (or its macro group doesn't exist, or the macro is new but hasn't
been saved.
Note that when you enter the macrogroupname.acroname syntax in an argument,
you must specify the name the macro's macro group was last saved under.
*

Basically - I have no idea what I'm doing.
-Josh

 

[Josh Sharpe]

Okay so I created two quick bound forms the first one has a button to open
the second one. The second one has a button with the code you gave me in
the onClick event.

I'm getting the following error when i click the button on the second form:

*
Microsoft Access can't find the macro
'Forms!frmNewFaculty!AddressID=Me!AddressID'
*

Basically - I have no idea what I'm doing.

-Josh

 

[Joshua A. Booker]

Josh,

Don't fret...you'll get it.

In the property sheet you can enter a Macro name, Function Name or choose
[Event Procedure]. [Event Procedure] runs the code behind the form for that
event.

In the property sheet, next to OnClick, you choose [Event Procedure]. Then
click on the Ellipsis (3dots) and this will open the VBA Code editor.
Access begins the event procedure and places your curser within it. Paste
the text into the procedure and you're done. It should look something like
this:

Private Sub YourButtonName_Click()

Forms!frmNewFaculty!AddressID=Me!AddressID

End Sub

Good Luck,
Booker

 

[Josh Sharpe]

It worked!! Very Excited!!
Thanx to much.

Now I have another question, I dont think this is as difficult.
Now that I've moved this primary key over to the main form I want the main
form to use the key to lookup that record in the table and put the data into
the appropriate fields.
This main form is bound, but it's bound to a different table than the second
one was. So these fields that are going ot be updated will be unbound
fields(so they should be updatable) Should i just use an update query on the
primary key value?

-Josh

Josh,

Don't fret...you'll get it.

In the property sheet you can enter a Macro name, Function Name or choose
[Event Procedure]. [Event Procedure] runs the code behind the form for that
event.

In the property sheet, next to OnClick, you choose [Event Procedure]. Then
click on the Ellipsis (3dots) and this will open the VBA Code editor.
Access begins the event procedure and places your curser within it. Paste
the text into the procedure and you're done. It should look something like
this:

Private Sub YourButtonName_Click()

Forms!frmNewFaculty!AddressID=Me!AddressID

End Sub

Good Luck,
Booker

Okay so I created two quick bound forms the first one has a button to open
the second one. The second one has a button with the code you gave me in
the onClick event.

I'm getting the following error when i click the button on the second form:

*
Microsoft Access can't find the macro
'Forms!frmNewFaculty!AddressID=Me!AddressID'
*

Basically - I have no idea what I'm doing.

-Josh



have work
for

the

ID lot on
the can't
help

form.

I

KeyFieldName

is

 

[Joshua A. Booker]

Josh,

It's good practice in databases to avoid storing the same information in
more than one place. I gather form your field names that you're dealing
with address data. Typically, if you have a table with addresses, you can
use the ID to get the information from that that table. It sounds like this
calls for a subform on the main form that looks at the addresses table.
Doing so will save only the ID in the main table and display the details
from the address table having that ID.

If you must duplicate the data into the master table, you can use an update
query or you can use the same method as you did with the ID field like so:

Private Sub YourButtonName_Click()

Forms!frmNewFaculty!AddressID=Me!AddressID
Forms!frmNewFaculty!Street=Me!Street
Forms!frmNewFaculty!City=Me!City
Forms!frmNewFaculty!State=Me!State
Forms!frmNewFaculty!Zip=Me!Zip

End Sub

Assuming both forms have fields or controls named Street, City, State, and
Zip.

HTH,
Josh

It worked!! Very Excited!!
Thanx to much.

Now I have another question, I dont think this is as difficult.
Now that I've moved this primary key over to the main form I want the main
form to use the key to lookup that record in the table and put the data into
the appropriate fields.
This main form is bound, but it's bound to a different table than the second
one was. So these fields that are going ot be updated will be unbound
fields(so they should be updatable) Should i just use an update query on the
primary key value?

-Josh

Josh,

Don't fret...you'll get it.

In the property sheet you can enter a Macro name, Function Name or choose
[Event Procedure]. [Event Procedure] runs the code behind the form for that
event.

In the property sheet, next to OnClick, you choose [Event Procedure]. Then
click on the Ellipsis (3dots) and this will open the VBA Code editor.
Access begins the event procedure and places your curser within it. Paste
the text into the procedure and you're done. It should look something like
this:

Private Sub YourButtonName_Click()

Forms!frmNewFaculty!AddressID=Me!AddressID

End Sub

Good Luck,
Booker

Okay so I created two quick bound forms the first one has a button to open
the second one. The second one has a button with the code you gave me in
the onClick event.

I'm getting the following error when i click the button on the second form:

*
Microsoft Access can't find the macro
'Forms!frmNewFaculty!AddressID=Me!AddressID'
*

Basically - I have no idea what I'm doing.

-Josh



Josh,

In fact, it does get more simple than that. I my opinion, bound

forms
and

controls are simpler than unbound for several reasons. One reason

is
that

you won't have to worry about using queries to insert or update records.
After all, that's what Access is designed to do for you. Unless you have
a
good reason to use unbound forms, I'd suggest letting Access do the work
for
you.

If the first form is unbound, you should not get the 'not updateable'
error.
An unbound textbox (one having a blank controlsource property) is always
updateable and that error usually is referring to whether the form's
recordsource is updatable. An unbound form (blank recordsource property)
has no recordsource.

My suggestion is for bound forms and controls. Using SQL to lookup

the

ID
and again to update the field, then again the redisplay the values

on
the

first form is the only way to do it on an unbound form. Seems like

a
lot

of
work. Especially when Access does it faster and more accurately by itself
if you simply use bound forms.

HTH,
Josh
The form is unbound, the field is unbound and I use a query to update
with.
I'm not sure if it gets more simple than this. I'm also not sure what
info
you need to help answer it. So let me know and I'll be glad to answer.
-Josh

Josh,

If you are using Access db table (not SQL) and the PK is an autonumber
field, the autonumber is generated before the record is saved. Access
inserts the record after the first letter is typed into any

field

on field
in the
new form. KeyFieldName

button

to complete
the from.
So