goto new record in (Datasheet View) not working

[smercer]

Hi all

I am having a strange problem, and that is the line

DoCmd.GoToRecord , , acNewRec

is not working. What happens is when the user scans a barcode, this coding will overwrite the first record (the only record) and the record in the sub form. which means that the form is completely ignoring the go to record lines.

What is supposed to happen is each time the user scans a book, there will be a new temporary record until the end of the sale.

If IsNull(DLookup("[With_ISBN_Number]", "tbl_Current_Sales_For_Book_Des", "[ISBN_Number]=[With_ISBN_Number]")) Then GoTo first_ISBN
If Not IsNull(DLookup("[With_ISBN_Number]", "tbl_Current_Sales_For_Book_Des", "[ISBN_Number]=[With_ISBN_Number]")) Then GoTo ISBN_exists



first_ISBN:
    'if there is no book copies (no ISBN Number the same) exist then
    'This section is for the copying the sfrm_Current_Sales_For_Book_Des data over to sales form
    Me!sfrm_Current_Sales_For_Book_Des.SetFocus
    DoCmd.GoToRecord , , acNewRec
    Me!sfrm_Current_Sales_For_Book_Des!ISBN_Number = Me!sfrm_Current_Sales_Book_Description!ISBN_Number
    Me!sfrm_Current_Sales_For_Book_Des!Title = Me!sfrm_Current_Sales_Book_Description!Title
    Me!sfrm_Current_Sales_For_Book_Des!Author_Surname = Me!sfrm_Current_Sales_Book_Description!Author_Surname
    Me!sfrm_Current_Sales_For_Book_Des!Author_FName = Me!sfrm_Current_Sales_Book_Description!Author_FName
    Me!sfrm_Current_Sales_For_Book_Des!Series = Me!sfrm_Current_Sales_Book_Description!Series
    Me!sfrm_Current_Sales_For_Book_Des!Category = Me!sfrm_Current_Sales_Book_Description!Category
    Me!sfrm_Current_Sales_For_Book_Des!Sub_Category = Me!sfrm_Current_Sales_Book_Description!Sub_Category
    DoCmd.RunCommand acCmdSelectRecord
              
        
    'and now for the each record in the Each_Book Subform
    Me!sfrm_Current_Sales_For_Book_Des!sfrm_Current_Sales_For_Each_Book_Datasheet_View.SetFocus
    Me!sfrm_Current_Sales_For_Book_Des!sfrm_Current_Sales_For_Each_Book_Datasheet_View!Book_ID = Me!sfrm_Current_Sales_Book_Description!sfrm_Each_Book_Sales_Selected!Book_ID
    Me!sfrm_Current_Sales_For_Book_Des!sfrm_Current_Sales_For_Each_Book_Datasheet_View!ISBN_Number = Me!sfrm_Current_Sales_Book_Description!sfrm_Each_Book_Sales_Selected!ISBN_Number
    Me!sfrm_Current_Sales_For_Book_Des!sfrm_Current_Sales_For_Each_Book_Datasheet_View!Cover = Me!sfrm_Current_Sales_Book_Description!sfrm_Each_Book_Sales_Selected!Cover
    Me!sfrm_Current_Sales_For_Book_Des!sfrm_Current_Sales_For_Each_Book_Datasheet_View!Cost_Price = Me!sfrm_Current_Sales_Book_Description!sfrm_Each_Book_Sales_Selected!Cost_Price
    Me!sfrm_Current_Sales_For_Book_Des!sfrm_Current_Sales_For_Each_Book_Datasheet_View!Sale_Price = Me!sfrm_Current_Sales_Book_Description!sfrm_Each_Book_Sales_Selected!Sale_Price
    DoCmd.RunCommand acCmdSelectRecord
    GoTo End_Me

 

[KenHigg]

Did you try throwing a doEvent in?

 

[smercer]

Did you try throwing a doEvent in?

No I did not. And I don't know how to either. could you please tell me what it does?

By the way, your dlookup worked wonders thanks!!!

 

[KenHigg]

After the GotoRecord command, just type in Doevents. I really doubt it will fix your problem but it's worth a shot...

Dlookup - Mile-Phile did a custom function to replace this which appears to be faster if you need performance. I think he posted it in the vb section of this forum...

ken

 

[smercer]

After the GotoRecord command, just type in Doevents. I really doubt it will fix your problem but it's worth a shot...

you mean like this:
DoCmd.RunCommand acCmdRecordsGoToNew
already tried that one. Yes it worked once another time so I know what your geting at.

thanks for responding

 

[KenHigg]

Hope this makes sense...


Me!sfrm_Current_Sales_For_Book_Des.SetFocus
DoCmd.GoToRecord , , acNewRec
DoEvents
Me!sfrm_Current_Sales_For_Book_Des!ISBN_Number = Me!sfrm_Current_Sales_Book_Description!ISBN_Number
Me!sfrm_Current_Sales_For_Book_Des!Title = Me!sfrm_Current_Sales_Book_Description!Title


ken

 

[smercer]

Hope this makes sense...


Me!sfrm_Current_Sales_For_Book_Des.SetFocus
DoCmd.GoToRecord , , acNewRec
DoEvents
Me!sfrm_Current_Sales_For_Book_Des!ISBN_Number = Me!sfrm_Current_Sales_Book_Description!ISBN_Number
Me!sfrm_Current_Sales_For_Book_Des!Title = Me!sfrm_Current_Sales_Book_Description!Title


ken

Sorry DoEvents did not work (same as before)

Thanks anyway

 

[KenHigg]

Failing anyone elses suggestion:

I'd move all the code out of the routine and store it in a .txt file. The try placing just the DoCmd.GoToRecord code back in and see if it works by itself.

If it does work, then try placing small chunks of the original code back into the routine, testing as you go, untill the problem pops up again. This may provide you with a starting point to trouble shoot as you potentially eliminated a lot posibilities.

If it still does not work, the same is true except you've eliminated a different set of posible issues...

Make sense???

ken

 

[smercer]

I changed the code to this:

first_ISBN:
    'if there is no book copies (no ISBN Number the same) exist then
    'This section is for the copying the sfrm_Current_Sales_For_Book_Des data over to sales form
    Me!sfrm_Current_Sales_For_Book_Des.SetFocus
    DoCmd.GoToRecord , , acNewRec
        
    'Me!sfrm_Current_Sales_For_Book_Des!ISBN_Number = Me!sfrm_Current_Sales_Book_Description!ISBN_Number
    'Me!sfrm_Current_Sales_For_Book_Des!Title = Me!sfrm_Current_Sales_Book_Description!Title
    'Me!sfrm_Current_Sales_For_Book_Des!Author_Surname = Me!sfrm_Current_Sales_Book_Description!Author_Surname
    'Me!sfrm_Current_Sales_For_Book_Des!Author_FName = Me!sfrm_Current_Sales_Book_Description!Author_FName
    'Me!sfrm_Current_Sales_For_Book_Des!Series = Me!sfrm_Current_Sales_Book_Description!Series
    'Me!sfrm_Current_Sales_For_Book_Des!Category = Me!sfrm_Current_Sales_Book_Description!Category
    'Me!sfrm_Current_Sales_For_Book_Des!Sub_Category = Me!sfrm_Current_Sales_Book_Description!Sub_Category
    'DoCmd.RunCommand acCmdSelectRecord
              
        
    'and now for the each record in the Each_Book Subform
    'Me!sfrm_Current_Sales_For_Book_Des!sfrm_Current_Sales_For_Each_Book_Datasheet_View.SetFocus
    'Me!sfrm_Current_Sales_For_Book_Des!sfrm_Current_Sales_For_Each_Book_Datasheet_View!Book_ID = Me!sfrm_Current_Sales_Book_Description!sfrm_Each_Book_Sales_Selected!Book_ID
    'Me!sfrm_Current_Sales_For_Book_Des!sfrm_Current_Sales_For_Each_Book_Datasheet_View!ISBN_Number = Me!sfrm_Current_Sales_Book_Description!sfrm_Each_Book_Sales_Selected!ISBN_Number
    'Me!sfrm_Current_Sales_For_Book_Des!sfrm_Current_Sales_For_Each_Book_Datasheet_View!Cover = Me!sfrm_Current_Sales_Book_Description!sfrm_Each_Book_Sales_Selected!Cover
    'Me!sfrm_Current_Sales_For_Book_Des!sfrm_Current_Sales_For_Each_Book_Datasheet_View!Cost_Price = Me!sfrm_Current_Sales_Book_Description!sfrm_Each_Book_Sales_Selected!Cost_Price
    'Me!sfrm_Current_Sales_For_Book_Des!sfrm_Current_Sales_For_Each_Book_Datasheet_View!Sale_Price = Me!sfrm_Current_Sales_Book_Description!sfrm_Each_Book_Sales_Selected!Sale_Price
    'DoCmd.RunCommand acCmdSelectRecord
    
    GoTo End_Me

I need to have the subform to set focus before I can go to new record. every other line in the if statement has been changed to comments. Do you want me to change all lines in entire timer event?

 

[KenHigg]

I assume it still failed?

 

[smercer]

I assume it still failed?

sorry I forgot to say no it didn't work.

 

[KenHigg]

Well I would guess that you've eliminated those lines as the problem! I would contniue to comment out logical chunks of code until it works...

???
ken

 

[smercer]

Well I would guess that you've eliminated those lines as the problem! I would contniue to comment out logical chunks of code until it works...

???
ken

It was the Dlookup that was the problem. Had it in the IF statement like this:

If IsNull(DLookup("[With_ISBN_Number]", "tbl_Current_Sales_For_Book_Des", "[ISBN_Number]=[With_ISBN_Number]")) Then

changed to:

If IsNull(Me!ISBN_Lookup.Value) Then

and had the dlookup change the ISBN_Lookup.

**Edit**

I thought it worked because it worked once and is still doing overwriting last record.

**End edit**

But now have new problem. "Can't go to specified record" error. Is there a way to make the code to go to next line (ignore error) on this particular error?

Thanks Ken

 

[smercer]

The reason why it was still not working was the

On Error Resume Next

line I had put in to stop it from coming up with error code because the recordset was at the destination record.

I am now working on a case select statement