Mutliple Carryovers

[troix]

I am trying to write in VB a procedure (or whatever it's called) where CGID, CG First Name AND CG Last Name from the original form will go into the NEW form. I can only get CGID to go in as of now. These are the names in BOTH forms.

I know adding new StLinkCriteria and DoCmd.SQL isn't working

This is what I have for just CGID.


---
Private Sub CGAGDS_Click()
On Error GoTo Err_CGAGDS_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "CG_AGDS"
stLinkCriteria = "[CGID]=" & Me![CGID]
DoCmd.SetWarnings False
DoCmd.RunSQL "Insert Into CG_AGDS(CGID) VALUES (" & Me![CGID] & &quot"
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.Save
DoCmd.SetWarnings True

Exit_CGAGDS_Click:
Exit Sub

Err_CGAGDS_Click:
MsgBox Err.Description
Resume Exit_CGAGDS_Click

End Sub
---

Thanks

 

[jimb0ne]

rather than using SQL, why don't you just create a new record with the values from the old form when the new form opens? You could just pass the 3 values in stLinkCriteria and then on open of the new form check for the openargs property, and create a new record using me.recordset. Would be fairly simple, and the current record would be the one that was just created.

Something like :

docmd.openform stDocname,,, stLinkCriteria

then on the open event of the other form:

dim ArgsArray() as string
if me.openargs <> &quot;&quot; then
argsarray() = split(me.openargs, &quot;,&quot
end if
with me.recordset
.addnew
!CGID = argsarray(0)
!CGFirstName = Argsarray(1)
!CGLastName = argsarray(2)
.update
.bookmark = .lastmodified
end with

that should do the trick, as long as you pass the values in stLinkCriteria seperated by commas, so basically CGID, CGFirstName, CGLastName Let me know if this works for you.

 

[troix]

This is so above my head. I'm sorry, it still doesn't make sense to me.

 

[jimb0ne]

No worries. I guess my first question is: are the two forms that you are using bound to different tables? meaning, are their record source properties different? and are they tables? or queries? If they are tables the above code would work fine, you just have to let me know how you have things set up, names of controls, fields in tables, etc. and I'll write a piece of code that should do everything you need.

 

[troix]

if you could, can you AIM me troix1 it'd be easier to do that there maybe.

 

[jimb0ne]

I don't have AIM, I'm at work right now, and I don't have an AIM account to begin with. We'll have to work thru the forum, but just try to explain as best you can how you have it set up and I'll do my best to write code and make it more self explanatory.

 

[troix]

I have Two different tables
&quot;Care Giver FIles&quot;
AND
&quot;CG_AGDS&quot;

in Care Giver Files, someone will enter much information. Included is &quot;CGID&quot; (which is an Autonumber), &quot;CG First Name&quot; and &quot;CG Last Name&quot;

After they enter that information, when they click the button that says &quot;CG_AGDS&quot; i need the 3 fields CGID, CG First Name and CG Last Name to be auto filled in with the SAME information that has just been entered. The original form (Care Giver Files) MUST remain open.

 

[jimb0ne]

uhh, so when someone clicks on CG_AGDS, the record source for that form is the table &quot;CG_AGDS&quot;? ok, well here is a way to go about it which is similar to your above solution. I'm not sure what the names of the CG First Name and CG Last Name fields are on your Care Giver Files form, but I'll assume they are the same, except without spaces(eg. CGLastName) here we go:

place the following code in the button that says &quot;CG_AGDS&quot; click event:

dim db as dao.database
dim rs as dao.recordset

set db = currentdb
set rs = openrecordset(&quot;SELECT * FROM CG_AGDS;&quot

with rs
.addnew
!CGID = me.recordset!CGID
!CGFirstName = me.recordset!CGFirstName
!CGLastName = me.recordset!CGLastName
.update
.bookmark = .lastmodified
end with

docmd.openform &quot;CG_AGDS&quot;,,,&quot;CGID = &quot; & me!CGID
rs.close
db.close

this will open the CG_AGDS form and show the record that was just added. Does this help you? Let me know. that should be all you need reall, oh yeah, in the visual basic editor, go to the tools menu, then to references and make sure &quot;Microsoft DAO 3.6 Objects Library&quot; is checked.

 

[troix]

Maybe that's the problem is I have spaces in them.

but those are the ONLY 3 things I want carried from CGA FILES to CG_AGDS.

CG Files has 90 fields.

 

[jimb0ne]

the code I placed there will basically add a new record to the CG_AGDS table everytime someone opens the form. If you want to check for the values then if they don't exist, add them, there is a bit more code to consider. Is the code I placed helpful at all or are you still stuck?

 

[troix]

i'm still stuck. let me try to explain it again

In Care Giver Files, CGID will be AutoNumber. CG First Name (WITH spaces <it HAS to have spaces {those are the rules given to me}>) and CG Last Name.

When I click &quot;CG_AGDS&quot;, those 3 values JUST entered have to go into the form automatically. Other data will be entered into CG_AGDS that is NOT in Care Giver Files.