VBA Go to a record after an SQL insert (1 Viewer)

[Chrism2]

Hello folks,

I have a an unbound popup form (popNewCustomer) that allows the input of data into a Customer Table (tblCustomers).

Once the form has been completed, I have a sub that puts the completed forms data into the table via an SQL Insert. All good.

The form then closes.

I'd like to automatically navigate to the record in question in the (always open) main form (frmMainDisplay) - but because I'm using SQL to insert the record, I don't know it's CustomerID. I do have a plethora of information to the tune of the variables I've just inserted - but no ID.... (Here's the SQL - the "var$$$$$" outputs the variable with single quotes so SQL can deal with it.

trSQLstatement = "" ' to be sure it starts blank
        strSQLstatement = strSQLstatement & "INSERT INTO tblCustomers ( CompanyName, AddressL1, AddressL2, AddressL3, City, PostalCode, MainTel, MainFax, InceptionDate, BoolClient) "
        strSQLstatement = strSQLstatement & "SELECT " & varCustomer & " AS CompanyName, "
        strSQLstatement = strSQLstatement & varAddrL1 & " AS AddressL1, "
        strSQLstatement = strSQLstatement & varAddrL2 & " AS AddressL2, "
        strSQLstatement = strSQLstatement & varAddrL3 & " AS AddressL3, "
        strSQLstatement = strSQLstatement & varCity & " AS City, "
        strSQLstatement = strSQLstatement & varPostcode & " AS PostalCode, "
        strSQLstatement = strSQLstatement & varTel & " AS MainTel, "
        strSQLstatement = strSQLstatement & varFax & " AS MainFax, "
        strSQLstatement = strSQLstatement & varInception & " AS InceptionDate, "
        strSQLstatement = strSQLstatement & varClient & " AS BoolClient"

So, in summary, I just need to either navigate to the latest created record or the record relating the one just made.

Can anyone help me please?

 

[JamesMcS]

use docmd.gotorecord,, aclast I think, but I've not used it outside of navigating through records in forms....

 

[Chrism2]

use docmd.gotorecord,, aclast I think, but I've not used it outside of navigating through records in forms....

Thanks James. It's close, but not quite.

It certainly does go to the last record - but the last *before* the one we've just made.

I'm calling this code - to see if it makes a difference

Public Sub RefreshMainForm()

Forms!frmMaindisplay.Form.Refresh
Forms!frmMaindisplay!sfrmCustomerList.Form.Requery

DoCmd.RunCommand acCmdRefresh

End Sub

and then I'm going with

DoCmd.GoToRecord , , acLast

But still it goes to the last previous entry...

 

[JamesMcS]

Hmm can you requery/refresh the table the data's stored in too?

 

[darbid]

does this link help

http://www.access-programmers.co.uk/forums/showthread.php?t=169609

 

[Chrism2]

does this link help

http://www.access-programmers.co.uk/forums/showthread.php?t=169609

I suspect

dim lngNewID as Long
dim rs as DAO.Recordset
 
set rs = currentdb.openrecordset("MyTable")
rs.AddNew
 
lngNewID = rs!MyTableID
'Add other fields
rs.Update
rs.Close
set rs = nothing

May help... though I'll need to look at how I convert my SQL code to work with this and inject it into the 'Add other fields bit.

Cheers.

 

[darbid]

By using an insert into you are making it hard on yourself. If you use a recordset it is easy.

 

[boblarson]

You might want to read this post by Access MVP Leigh Purvis where in the last third of the post shows how to use the @@identity to get the last autonumber added.

 

[Chrism2]

By using an insert into you are making it hard on yourself. If you use a recordset it is easy.

Quite possibly; point taken.

However, I'm not sure my issue lies sqaurely with the method, but more with an issue on my main form. (Attached DB),

With no customer data, the problem is easy to replicate. Use the Create New Customer function to insert a new record - no problem - the new customer record appears in sfrmCustomerList - but you if you try to navigate to the record, we get a "No Current Record error".

I can't see the issue here myself - is it obvious to any of you (very helpful) peeps?

Thanks, everyone, for helping me and for the previous responses.

 

[Chrism2]

Ah, fixed it.

The sfrm in frmMainDisplay didn't have a link to it's parent. (I.e. broken)

Added it and things work the way I need them (once I used some of the suggestions above).

Thanks again folks

 

[boblarson]

Thanks for posting back about your success.