field from other table

  • Thread starter Thread starter mark r
  • Start date Start date
M

mark r

I copied a list box from a sub form
I pasted it into the main form
I changed it to a combo box
I typed =subform_field name in to control source
It looks fine
It drops down fine
but when I select the row for data entry
the drop down does not drop up and the data is not
selected and stored.


what is wrong?
 
Mark,

What does =subform_field name mean?

If the Control Source of the combobox is not an updateable field which
is included in the Record Source of the main form, you won't be able to
use it to enter/edit data... it just doesn't work like that!

If you want a combobox on the main form, and for the value selected to
end up in the table that the subform is based on, this would be possible
by making the combobox unbound, and then using a VBA procedure on its
After Update event to "put" the data into the subform record. But this
would normally seem unnecessarily complicated compared with just putting
the combobox back on the subform.
 
making the combobox unbound, and then using a VBA
procedure on its After Update event to "put" the data
into the subform record.
Click to expand...


Combobox_Afterupdate()

Set rs= NotMainTableName.recordsetclone

..update
field =
 
making the combobox unbound, and then using a VBA
procedure on its After Update event to "put" the data
into the subform record.
Click to expand...


Combobox_Afterupdate()

Set rs= NotMainTableName.recordsetclone

..update
field =
 
making the combobox unbound, and then using a VBA
procedure on its After Update event to "put" the data
into the subform record.
Click to expand...


Combobox_Afterupdate()

Set rs= NotMainTableName.recordsetclone

..update
field =
 
by making the combobox unbound, and then using a VBA
procedure on its After Update event to "put" the data into
the subform record.

Combobox_Afterupdate()

Set rs = NotMainTableName.recordsetclone

With rs
.Find "ID = " & Me.ID
.Edit
NotMainTableName!insdatafieldname = unbounddataheld
.Update
End With

=============
This is a skeleton , but specifically what do I have to
do?
 
Sorry about the extra entries, I hit the tab button by
accident a few times and entered a few times.

I appreciate the late night response, Are you west coast
or just up late?
 
Mark,

I can't really answer because I know nothing about what your main form
and subform are all about, or what you want to achieve. However, it
looks like you might want to set the value of all related subform
records... is that right? If so, I would do this...

Combobox_Afterupdate()
CurrentDb.Execute "UPDATE SubTable SET YourField ='" & Me.MyCombo &
"' WHERE ID=" & Me.ID
End Sub
 
Mark,

No problem.

Not really late night... I'm in New Zealand. It's 5:23 pm Saturday
here at the moment :-)
 
why is recordsetclone not used?
what is CurrentDb collection?
why is a single quote mark used?
looks like you might want to set the value of all related
Click to expand...
subform >records... is that right? If so, I would do
this...

There is a child table that has a subform. There is one
field that I also want to be able to be viewed and updated
on the mainform, for the current autoID being viewed. I
suppose if I attempt to make a value selection for this
child field in the child table, ACCESS will create a new
child record for that autoID in the child table, even
though the data input is not occuring from the subform.
 
Mark,

mark said:
why is recordsetclone not used?
Click to expand...
It's not applicable to the situation.
what is CurrentDb collection?
Click to expand...
Current database
why is a single quote mark used?
Click to expand...
I made an assumption that the value you want to update into the child
table is text. Maybe I'm wrong, I don't know, but it was my best guess
at the time, since you haven't given much information about what you are
really doing.
There is a child table that has a subform. There is one
field that I also want to be able to be viewed and updated
on the mainform, for the current autoID being viewed. I
suppose if I attempt to make a value selection for this
child field in the child table, ACCESS will create a new
child record for that autoID in the child table, even
though the data input is not occuring from the subform.
Click to expand...

Maybe an example with actual data would help you to convey your meaning.
 
Okay, I get it now. And it seems to be working well. That
is, the data in the underlying maintable and childtable
are changing correctly.

Question 1:

I decided to put the CurrentDb.Execute code into
Combo_Lostfocus() rather than _Afterupdate (I hope that
is not a problem - do I lose any advantages?)


Question 2:

One functional gliche I still have is that when the user
changes the childfield value from the subform and then
returns to the mainform, the mainformcombobox shows NO
value, rather than the new value selected from the
subformcombobox.

One reason, I suspect, is that the control source of the
mainformcombobox is empty. So I tried putting in
1. subformname!childfieldname (ins_data!ins_paid_how)
2. =subformname!childfieldname (=ins_data!ins_paid_how)
3.[subformname]![childfldname] ([ins_data]![ins_paid_how])
4.=[subfmnm]![childfldname] (=[ins_data]![ins_paid_how)

And I tried putting Me!mainformcombobox.Requery in the
closecommandbutton of the subform.
 
Back
Top