Re: Receive a 3134 error

That Worked!!!! Thank you so much! As far as the lookup column, I can't
stand that myself. I am finishing what someone started and they didn't know
any vba. I am kind new to access myself. How do I remove the lookup column?

Dirk Goldgar wrote:
Thanks for the reply, but I it might have something to do with my
table. In my table in one of the columns, there is a drop down menu.
In the the menu, the data is not the data that belongs in that
column. It looks likes the index number instead of the locations.
How do I delete filters from the table? I am still getting the
insert into error (error 3134).

You have a lookup field in your table, and that's definitely something
you have to deal with. However, I don't believe that is the source of
your 3134 error ("Syntax error in INSERT INTO statement"). Your lookup
field is likely to give you a type mismatch error when you execute the
INSERT, but you are not yet getting that far.

The code you posted will *definitely* give you the 3134 error, because
it results in a VALUES clause like this:

VALUES(123, , 'ABC', , #8/21/2006#)

The missing fields represented by consecutive commas are invalid syntax.
Instead, the clause must look like this:

VALUES(123, Null, 'ABC', Null, #8/21/2006#)

That's what the function I posted is designed to do: put the proper
delimiters around a value, and substitute the "Null" literal when there
is no value.

Dealing with the lookup fields is a separate issue. Most professional
developers don't like lookup fields, because of just this sort of
problem -- what is stored is not what is displayed. It has nothing to
do with filters, unless I'm completely misunderstanding what you've
posted. With lookup fields, you have to make sure that the value you
actually insert in the table is what the field is *storing*, not
necessarily what it is displaying. I don't know enough about your
database application to give you more explicit advice than that. Maybe
after you've got past the 3134 error, and start getting a type mismatch
error instead <g>, you can make a new post on the subject.


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/200608/1

.

Relevant Pages

  • Re: Storing Foreign Key in a table
    ... for now to agree that lookup fields are to be avoided. ... I can't make out what you mean by storing "the text of the foreign key to ... My combo boxes load but do not display a value. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Problem with Subform not displaying correct data
    ... your problem was that you were displaying the wrong column. ... can do that with both List boxes and Combo boxes. ... against using lookup fields ... >> that the problem is caused by the Lookup fields in the tables. ...
    (microsoft.public.access.forms)
  • Re: Help-How to select multiple values using lookup function???
    ... the lookup fields in Project are not multi-select fields. ... may be possible through enterprise fields using Project Server, ... With the lookup function, it will only allow 1 selection. ...
    (microsoft.public.project)
  • Re: An unknown error has occured...
    ... Do you have lookup fields defined? ... when you run out of the IDE with debug set to stop on errors. ... (Project raised exception class EOleException with message "Ett okänt ...
    (borland.public.delphi.database.ado)
  • Re: Subform Combo Requery - Hair pulling stage!!
    ... >My continuous subform shows a list of products that are in stock. ... >Product Lookup Model Serial Received In Stock Comments ... >selected with the lookup column and In Stock is set to "No" for this ...
    (microsoft.public.access.formscoding)
Loading