Solved

Add a value to a combobox in Access Project Sql Server Backend

Posted on 2006-10-26
2
174 Views
Last Modified: 2011-09-20
I am work with this script that I found on this site however I am getting an error at this point in the script.

Set RS = MyDataBase.OpenRecordset("dbo.Client")

I can get it to respond and open a msg/input box where I can add the the new item to Client_Name  (a text Field column two).  The combo box is bound to column one Client_ID(autonumber) and the table is Client.  What I need is for the person to be able to add a new item to this lookup table if it does not exist and choose that item.



The full script is


Private Sub Targeted_Customers_AfterUpdate()
Customer_ID_Update = Date

Dim ListData As String, Message As String
Dim MyDB As Database, RS As Recordset

If [Targeted_Customers].Column(1) = "<Add New Item>" Then

    Message = "Please Enter a New Item"    ' Set prompt.
    ' Display message
    ListData = InputBox(Message)

If Not IsNull(ListData) Then

Set MyDataBase = CurrentDb()
Set RS = MyDataBase.OpenRecordset("dbo.Client")

RS.AddNew
    RS("Client_Name") = ListData
RS.Update

RS.Close
Set RS = Nothing
db.Close
Set db = Nothing

End If

[Targeted_Customers].Requery  '<----Updates the list

End If




End Sub
0
Comment
Question by:peggyweber
  • 2
2 Comments
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17810915
That's DAO code - none of which will work in an ADP.
I take it your want an ADO equivalent?

(btw - IMHO if you're venturing into ADP development - you should give yourself a reasonable grounding in ADO first - unless you intend to use nothing but default functionality - which again I thinkis a mistake with server data.  But then I guess professional developers exist for a reason :-)
0
 
LVL 44

Accepted Solution

by:
Leigh Purvis earned 250 total points
ID: 17810930
Private Sub Targeted_Customers_AfterUpdate()
Customer_ID_Update = Date
Dim ListData As String

If [Targeted_Customers].Column(1) = "<Add New Item>" Then

    Message = "Please Enter a New Item"    ' Set prompt.
    ' Display message
    ListData = InputBox(Message)
    CurrentProject.Connection.Execute "INSERT INTO dbo.Client (Client_Name) VALUES '" &  ListData & "'"

End If

[Targeted_Customers].Requery
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question