How to start? Beginners question :/

[larissa]

Hello out there,

I'm trying to build a form in Access 2010 (mainly radiobuttons and checkboxes), which - when clicking a button - should be able to copy rows from one table to another in a given sequence (say, if checkbox1 is selected, we copy a row from form1 to form2 where value1 equals something and then we see whether checkbox2 is selected to find a row where value1 equals something else... and so on).

The thing is: I am entirely new to MS Access altogether.

Can someone give me a hint which macro commands etc I need to look for?

Thanks a lot,
Larissa

 

[rodmc]

me.[Forms].[YourForm].[Fieldtocopyinto] = [SourcefieldName]

if im understanding you correctly that is

 

[timf79]

Normaly data is stored in tables, so I am not sure why you would want to copy data from a form to a form?

If you want to extract data from one table and save in antother you can use a SQL for a append table querry in VBA.

Anyhow, I would stay away from Macros and do coding in VBA.
Even though it is harder in the beginning it will pey of in the long run.

 

[larissa]

Thank YOU!

Yeah you're absolutely right, the row is to be copied from one table to another, triggered by the form getting certain user inputs.

So, now I'm stuck somehow with my form's button's event procedure (Access keeps telling me runtime-error 424: object required, always highlighting the last line no matter how short it is).

myDb.execute "INSERT INTO table2 " _
& "(field1, field2, field3) " _
& "SELECT field1, field2, field3 " _
& "FROM table1 " _
& "WHERE field2 = 'value1'; "

Thanks for your kind help,
Larissa

 

[timf79]

Why don't you use:
DoCmd.RunSQL instead of myDB.execute
you also may want to turn of the notifications.

DoCmd.RunSQL "INSERT INTO table2 " _
& "(field1, field2, field3) " _
& "SELECT field1, field2, field3 " _
& "FROM table1 " _
& "WHERE field2 = 'value1'; "

 

[RenaG]

I am doing something similar so I would like to jump on this thread.

I created this SQL stmt and put it on the On Click event of the button:

DoCmd.RunSQL "INSERT INTO NavigatorInfoBreast " _
    & "(PatientID, EducationResourceID) " _
    & "SELECT Patient.ID, EducationResources.ResourceName " _
    & "FROM (Patient INNER JOIN EducationProgramXref ON " _
    & "Patient.ProgramID = EducationProgramXref.ProgramID) " _
    & "INNER JOIN EducationResources ON " _
    & "EducationProgramXref.EdResourceID = EducationResources.ID " _
    & "WHERE (((Patient.ID)=[Forms]![Patient]![PatID]) AND " _
    & "((EducationProgramXref.ProgramID)=[Patient].[ProgramID]));"

When I ran it, I got this error message:

Nurse Navigator Database set 12 field(s) to Null due to a type conversion faliure, and it didn't add 0 record(s) to the table due to key violations, 0 record(s) due to lock violations, and 0 record(s) due to validation rule violations.
Do you want to run the action query anyway? (etc)

One field missing in the Into list is the ID key field of the NavigatorInfoBreast. Do I need to reference it or will it automatically fill and number sequentially? Where do I look to resolve these errors?

TIA,
~RLG

 

[RenaG]

I found it. The Select should have said EducationResources.ID (instead of ResourceName).

I also added a Me.Recalc so that the newly added records would show in the form.

~RLG