Access crashing/auto quit the application after one record append by vba method

Anonymous
2024-02-27T18:09:14+00:00

I am using the below code to append query by VBA method.

It appends one record and crash/quits the access.

Dont understand, if my code is wrong, then it will not work, BUT how come it appends one records properly and then quits.

Please check my code and advise what correction can be done.

Dim db As DAO.Database

Dim rs1 As DAO.Recordset

Dim rs2 As DAO.Recordset

Dim sqlstr As String

sqlstr = "SELECT TableMasterLocal.* FROM TableMasterLocal LEFT JOIN TableMasterDV ON TableMasterLocal.SerialNo = TableMasterDV.SerialNo WHERE TableMasterDV.SerialNo Is Null;"

Set db = CurrentDb()

Set rs1 = db.OpenRecordset("TableMasterDV")

Set rs2 = db.OpenRecordset(sqlstr)

rs2.MoveFirst

Do Until rs2.EOF

rs1.AddNew

rs1!RecoNo = Nz(DMax("[RecoNo]", "[TableMasterDV]"), 0) + 1

rs1!Model = rs2!Model

rs1!SerialNo = rs2!SerialNo

rs1!SMR = rs2!SMR

rs1!WorkingHrs = rs2!WorkingHrs

rs1!ActWorkHrs = rs2!ActWorkHrs

rs1!TotalFuel = rs2!TotalFuel

rs1!FuelPerHr = rs2!FuelPerHr

rs1!Cautions = rs2!Cautions

rs1.Update

rs2.MoveNext

Loop

rs1.Close

rs2.Close

Set rs1 = Nothing

Set rs2 = Nothing

Set db = Nothing

Microsoft 365 and Office | Access | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments No comments

9 answers

Sort by: Oldest
  1. Anonymous
    2024-02-28T12:38:05+00:00

    Thanks for your advise, Instead of applying to main database, I tried to check the code in test database

    I used v3 for other field and so on will be using for more fields

    But it is getting error of 3061 and highlight the execute

    I suspect each V1, V2 etc need to be declared data types

    Note: ADO code avoiding as I don't know details about these coding and worried for future, In access in am aware of the coding , so preferred to by sql method

    Image

    Please guide the correction

    Was this answer helpful?

    0 comments No comments
  2. Anonymous
    2024-02-28T13:32:30+00:00

    Can you show your DDL about.table2? DDL:

    //drop table table2;

    create table table2 (RecoNo integer,Model integer,SerialNo integer);

    insert into table2 (RecoNo,Model,SerialNo) values (1,2,3);

    select * from table2;

    When you debug a sql statement,you would better begin with constants rather than varieties.

    I guess v2=rs2!Model

    rem Model is also a number

    your Model is text

    drop table table2;

    create table table2 (RecoNo integer,Model text,SerialNo integer);

    insert into table2 (RecoNo,Model,SerialNo) values (1,'m2',3);

    select * from table2;

    online sql test

    After the constant sql run successfully,you can concatenate sql with varieties like.

    sqlTemplate="insert into table2 (RecoNo,Model,SerialNo) values (1,'m2',3);"

    v1=3

    v2="some text"

    v3=4

    sql=replace(sqlTemplate,"1",v1)

    sql=replace(sql,"m2",v2)

    sql=replace(sql,"3",v3)

    debug.print sql

    db.execute sql

    Was this answer helpful?

    0 comments No comments
  3. Anonymous
    2024-02-28T14:53:04+00:00

    Sorry I getting confused.

    Below is the screen of my both table. And also the design view of one which is same for both

    Model is a text field.

    Note : I dont want the value to input manually.

    As you can understand, I am collecting the value from one table and using to append in another table.

    Suspect that do we need to declare V1 , V2 data type before using in the sql code, what can be the possible mistake

    As mentioned earlier, just to test the method, I created another database whose table is as below and error as posted above

    Image

    Image

    Was this answer helpful?

    0 comments No comments
  4. Anonymous
    2024-02-28T23:23:25+00:00

    As you can understand, I am collecting the value from one table and using to append in another table

    Yes,I see.

    But as I mentioned before.I 'd better step by step as run sql below one by one.

    you can paste them in QBE or db.execute one by one.

    drop table table2;

    create table table2 (RecoNo integer,Model text,SerialNo text);

    insert into table2 (RecoNo,Model,SerialNo) values (1,'m2','3');

    //this should be in QBE

    select * from table2;

    When you obtain the right result,try to replace constants with varieties in sql string.

    Was this answer helpful?

    0 comments No comments