Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2009
    Posts
    2

    Unanswered: Disable or Bypass Enter Parameter Value

    Hi, I am designing a database where a user imports an address data file which needs to be appended to a master address data file. The imported data has different field names on every occasion to the Master table as the data comes from our customers mailing data. I have created a form where the user maps the imported data fields to the Master fields which changes the field names in the imported table for the append section (as usually done in an append query where you choose the fields to append to). My problem is that not all the fields in the Master Table exist in the imported address table. So when I run the code to append the data I get 'Enter Parameter Value' for the missing fields (ie Temp.Fname). I need to be able to bypass this dialog box or tell the code to only append the fields which have been selected, I cannot change the fields in the append SQL code as they could all be there once mapped. Here is my append code...

    Dim sql_text As String

    sql_text = "INSERT INTO [Combined Data] ( SOURCE, TITLE, FNAME, SNAME, FULLNAME, SALUTATION, JOBTITLE, DEPARTMENT, COMPANY, A1, A2, A3, A4, A5, A6, A7, A8, A9, A10, POSTCODE, COUNTRY, OTHER1, OTHER2, OTHER3, OTHER4, OTHER5, OTHER6 ) SELECT Temp.SOURCE, Temp.TITLE, Temp.FNAME, Temp.SNAME, Temp.FULLNAME, Temp.SALUTATION, Temp.JOBTITLE, Temp.DEPARTMENT, Temp.COMPANY, Temp.A1, Temp.A2, Temp.A3, Temp.A4, Temp.A5, Temp.A6, Temp.A7, Temp.A8, Temp.A9, Temp.A10, Temp.POSTCODE, Temp.COUNTRY, Temp.OTHER1, Temp.OTHER2, Temp.OTHER3, Temp.OTHER4, Temp.OTHER5, Temp.OTHER6 FROM Temp;"

    DoCmd****nSQL sql_text

    The [Combined Data] table is my master table and the Temp table is what the user imported.

    Any help would be gratefully received.

    Thanks,

    Spikeydean.

  2. #2
    Join Date
    Jun 2009
    Posts
    2

    Talking Sorted

    Hi folks,

    I've solved my own problem. What I did was when I changed the field names in the code, there was an if statement which said if the data map was not null then change the field name, so I just added an else to create the field if it was null, as thus...

    If Me.fname <> "" Then CurrentDb().TableDefs("TEMP").Fields(Me.fname).Nam e = "FNAME" Else CurrentDb.Execute ("ALTER TABLE Temp ADD COLUMN FNAME Text;")

    Which then allowed the update query code to work as all the fields now exist.

    Only took me the best part of a day to work that out!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •