Help With this Code Please

[TurboDieselOne]

I need to Update The trelNametoAddress with new Name but Keep the Address I notice at the Bottom of the Code for Proc Sub cmdAddAddress there is some code that Creates a new Address related to the Company but I wish to Create a new Name related to the Address an update trelNametoAddress.


Private Sub cmdAddCompany_Click()
Dim x As Integer
x = MsgBox("Do You wish to Add this Company to the Database?", vbYesNo)
If x = vbYes Then
Dim strSql As String
Dim cmd As ADODB.Command

Set cmd = New ADODB.Command

strSql = "INSERT INTO tblNames ([Classification],[Name1],[CompanyWorkingFor],[Name2],[Name3],[Name4],[Keyword])" & _
" VALUES ('" & Me!cboClassification & "', '" & Me!cboName1 & "', '" & Me!cboCompanyWorkingFor & "', '" & Me!txtName2 & "', '" & Me!txtName3 & "', '" & Me!txtName4 & "', '" & Me!cboKeyword & "')"

cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandText = strSql
cmd.Execute
Else

DoCmd.CancelEvent
End If
End Sub




Private Sub cmdAddAddress_Click()
Dim x As Integer
x = MsgBox("Do You wish to Add this Record to the Database?", vbYesNo)
If x = vbYes Then
Dim strSql As String
Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset

Set rst = New ADODB.Recordset
Set cmd = New ADODB.Command

strSql = "INSERT INTO tblAddress (Name,Address1, Address2, Address3, Address4,City,Postcode,County,Country)" & _
" VALUES ('" & Me.cboAddressName1 & "', '" & Me.txtAddress1 & "', '" & Me.txtAddress2 & "', '" & Me.txtAddress3 & "', '" & Me.txtAddress4 & "', '" & Me.cboCity & "', '" & Me.cboPostcode & "', '" & Me.cboCounty & "', '" & Me.cboCountry & "')"


cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandText = strSql
cmd.Execute

rst.Open "tblAddress", CurrentProject.Connection, adOpenDynamic
rst.MoveLast

strSql = "INSERT INTO trelNameToAddress (NameId, AddressId)" & _
" VALUES ('" & Me.intNamesID & "', '" & rst!AddressID & "')"

cmd.CommandText = strSql
cmd.Execute
Else
DoCmd.CancelEvent
End If
Me.Form.Requery
End Sub

 

[Shadez]

I need to Update The trelNametoAddress with new Name but Keep the Address I notice at the Bottom of the Code for Proc Sub cmdAddAddress there is some code that Creates a new Address related to the Company but I wish to Create a new Name related to the Address an update trelNametoAddress.

Exactly what are your trying to say, what are you trying to do.


ps. It would help if you write using readable gammer and punctuation!

 

[Shadez]

Although i cant understand your post i have stream lined your code for you, it might help. But without knowing exactly what your problem is i cant help any more.



ShadeZ

Dim strSql As String 
dim rst as new adodb.recordset

Private Sub cmdAddAddress_Click() 
if  MsgBox("Do You wish to Add this Record to the Database?", vbYesNo)  = vbYes Then 


       strSql = "INSERT INTO tblAddress (Name,Address1,     Address2, Address3, Address4,City,Postcode,County,Country)" & _ 
                        " VALUES ('" & Me.cboAddressName1 & "', '" & Me.txtAddress1 & "', '" & Me.txtAddress2 & "', '" & Me.txtAddress3 & "', '" & Me.txtAddress4 & "', '" & Me.cboCity & "', '" & Me.cboPostcode & "', '" & Me.cboCounty & "', '" & Me.cboCountry & "')" 


        CurrentProject.Connection.execute strsql

        strsql = "select max(AddressID ) from tblAddress"
        rst.Open sqlstr, CurrentProject.Connection,     adOpenDynamic 
        if not rst.eof then

               strSql = "INSERT INTO trelNameToAddress (NameId, AddressId)" & _ 
                                 " VALUES ('" & Me.intNamesID & "', '" & rst!AddressID & "')"        
               CurrentProject.Connection.execute strsql
        end if
        rst.close
Else 
        DoCmd.CancelEvent 
End If 
set rst = nothing
Me.Form.Requery 
End Sub

Private Sub cmdAddCompany_Click() 

Dim strSql As String 

if MsgBox("Do You wish to Add this Company to the Database?", vbYesNo)  = vbYes Then 


        strSql = "INSERT INTO tblNames ([Classification],[Name1],[CompanyWorkingFor],[Name2],[Name3],[Name4],[Keyword])" & _ 
                          " VALUES ('" & Me!cboClassification & "', '" & Me!cboName1 & "', '" & Me!cboCompanyWorkingFor & "', '" & Me!txtName2 & "', '" & Me!txtName3 & "', '" & Me!txtName4 & "', '" & Me!cboKeyword & "')" 
        CurrentProject.Connection.execute sql
Else 

         DoCmd.CancelEvent 
End If 
End Sub

 

[TurboDieselOne]

The address one is OK but If the all I want to add to the recordset the Company I have to have code that adds a new record to the trelNametoAddress and Create a intNAmeID which is Pulled from the table Names When it adds a record to the table then inserts this into trelNamestoaddress and then then inserts the Current Displayed intAddressID into the Current trelNametoaddress so if the current intAddress ID = 4 and the new intNameID = 7 then the trelNametoaddress data will be NameID = 7, AddressID = 4 therefore this Name is related to this Address.