populate two tbls with one combo box

[ppoindexter]

tblONE has fldA, fldB fldC
tblTWO has fldA, fldX, fldZ
(fldA representing same data)

these tables are populated via a single form based on a query of tblONE and tblTWO

tblONE fldA is populated using a combo box

what i need to know is how do i populate tblTWO fldA simulaniously as tblONE fldA is populated

I am thinking some code will do the trick
any magicians looking my way?

 

[llkhoutx]

On fldA afterUndateEvent

dim db as dao.database
dim rs ad dao.recordset
set db=currentdb
set rs=db.openrecordset("tblTwo",dbopendynaset)
rs.addnew
rs!fldA=me!fldA
rs!fldX = ?
rs!fldz =?
rs.update
rs.close
db.close
set rs=nothing
set db=nothing

 

[ppoindexter]

thank you very much
i will try it first thing when i get to work in the a.m.

 

[ppoindexter]

below is the actual code i am using....this is resulting in some error messages....not sure if it matters but when i type in the code the first letter of each word is turning into a cap...i try to replace it with lower case but it keeps turning into a upper case letter..any ideas?



Private Sub ComboMIM_4_AfterUpdate()
Dim db As dao.Database
Dim rs As dao.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("tblmimtplan", dbOpenDynaset)
rs.AddNew
rs!Fldmimid = Me!Fldmimid
rs.Update
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
End Sub

 

[llkhoutx]

First, Access is not case sensitive, but will change the case of word/fields/objects it knows has been used.

A description of the error would help, but I suspect that you do not have the reference to the DAO Object Libirary set - in a form of module with VBA displayed, goto the Access Menu and select Tools|References and then be sure the Microsoft DAO 3.6 Object Library has been checked. Then click OK and try your code again.

Another possibility, without knowing what error your getting, is that the recordsetfields you are rreferenceing are misspelled.

Another possibility is that table tblmimtplan has a index key which is unique, is not allowed to be zero/null and accordingly, must be set for each record.

You really need to post the error message as your code appears correct to me.

 

[ppoindexter]

the dao lib was ok

i changed the relationship from enforcing ref integrity to not enforcing ref int....

that got it working...

but still have an issue (of another type)
i will try to work it out
if i cant i may be back knocking on your door

my apologies for not posting the error message
thanks for your help

 

[ppoindexter]

is there a way to add a select statement (?) to the code (posted above)?

i need to point to the specific record in tblmimtplan (tblTWO)
fldmimid (fldA) which must be populated

 

[llkhoutx]

After

Set rs = db.OpenRecordset("tblmimtplan", dbOpenDynaset)
rs.findfirst "FieldName=" & value ' for an integer value
rs.findfirst "FieldName='" & value & "'" for a string value

rs.edit 'instead of AddNew
rs!Fldmimid = Me!Fldmimid

 

[ppoindexter]

now i get this error

"Run time error '3077
Syntax error (missing operator) in expression"

i assumed i was suppose to use either the string or digit code whichever applied....was this wrong?

Private Sub ComboMIM_4_AfterUpdate()
Dim db As dao.Database
Dim rs As dao.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("tblmimtplan", dbOpenDynaset)
rs.FindFirst "fldtplanid=" & Value
rs.Edit
rs!Fldmimid = Me!Fldmimid
rs.Update
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
End Sub

 

[llkhoutx]

Where does "Value" come from. Your code assumes that it's numeric; strings must be enclosed in tic marks. The posted code doesn't have a value for "Value" unless "Value" is a numeric field on the active form.

Be ccertain that that record exists.

if rs.nomatch then ' traps not finding a record

 

[Mile-O]

Why would you want to put the same information into two different tables?

The fact that you are asking this question suggests some design problems.

 

[Kevin_S]

Have to agree with Mile - the instance of keeping the same data in two different tables suggests a possible design problem and that the db is not normalized. Try seaching on "normalization" in the forum for help on this...

HTH,
Kev

 

[ppoindexter]

sorry...but
i am getting a bit confused

the "value" should be the record that the form points to by making a selection in a combo box
......

i am trying to populate the record in tblmimtplan.fldmimid
whose value is selected from a combo box on the form that also populates the same field in a second table (tbl_mimtplan.fldmimid)

i can populate the tbl_mimtplan.fldmimid with no problem and i can even physically type in the record number in tblmimtplan.fldmimtplan.....however i would prefer some code to do this for me.....as of now the record is not passing to the fldmimtplan...and now i get the error message

 

[ppoindexter]

i was wondering when someone would ask about why duplicate the data... well....a form i have that aligns the data has code that pulls from tblmimtplan....as hard as i have tried i could not figure out how to pull it from tbl_mimtplan......

so since tblmimtplan.fldmimtplan was just hanging around in "null"ville i thought i would try to populate it....rather than struggle with the other.....and so it goes.....a snowball in access land

 

[llkhoutx]

Use the debugger to certain the "Value" is the value of your combo box.

 

[Kevin_S]

if value is supposed to be the value of the combo box then instead use:

me.yourcomboboxname.value

otherwise you will have to dimension the string name "value" and set its value:

Dim strValue as interger

strValue = me.combobox.value

is this what your after...?

HTH,
Kev

 

[ppoindexter]

yes...that was what i was after ...it works now ONLY on the first time that i add a record upon opening the form....i will play with it and see what i can do....thanks again (everyone) for your help