Need help using DLookup function and auto filling records

[LadyL]

Hi everyone,

I keep struggling with this Dlookup function. I simply want to fill a report entry with one value from a table, from record 1. I tried so many syntaxes that I simply don't know what to do any more. I now have this:

=DLookUp("number";"Administrative Data";"[IDS]=1")

The number is the field I want the data from in the table, administrative data is the name of the table, and IDS is the name of the ID field, where I need record 1 from.

However, it gives me an #Error. Any help?

And I would also like to create a button in my form to automatically fill a number of records (for example record 10 to 20 with a standard set of data (either filled in or data from a previous record). Could anyone point me in the direction on how to do this?



Thanks!

[June7]

Try commas instead of semi-colons. Because table name has space, enclose in []. Advise no spaces or special characters/punctuation (underscore is exception) in naming convention.

Use looping code. Example showing text, date, number type fields.

For x = 1 to Me.tbxLimit
CurrentDb.Execute "INSERT INTO tablename(field1, field3, field4) VALUES('" & Me.tbxOne & "', #" & Me.tbxTwo & "#, " & Me.tbxThree & ")"
Next

Or

Use code that sets DefaultValue property of controls so that when data entry is initiated in a new record, those values will populate. This will 'carry forward' data during data entry session.

[LadyL]

Thank you for your reaction June7, however if I use:

=DLookUp("[number]","[Administrative Data]","[IDS]=1")

I get a syntax error. I get a syntax error everytime I do not use ; instead of a comma.
Some more help? This is driving me nuts. I'll try the insert code after I figure this out.

[June7]

Are you non-U.S.? I think maybe non-U.S. versions of Access use semi-colon. So what happens now if you put back?

[LadyL]

If I use:

=DLookUp("[number]";"[Administrative Data]";"[IDS]=1")

I get an #Error again. I'm kinda desperate at the moment... Spent hours on this tiny problem.

And if I use:

=DLookUp("[number]";"[Administrative Data]";"[IDS]"=1)

I get #Type!

[June7]

Is IDS a number or text type field? If it is text then parameter needs apostrophe delimiters.

"[IDS]='1'"

I can suggest nothing else without knowing more about db. If you want to provide for analysis, follow instructions at bottom of my post.

Should not use reserved words as names. Number is a reserved word.

[June7]

I have only Windows Compression, however, the second file does open. If you want the files removed after issue is resolved, edit your posts.

Need to enclose [ID]=1 in quote marks. However, even that does not fix the error. Odd.

Will there ever be more than one record in that table?

I tested the DLookup in VBA immediate window with copy/paste of the expression from the textbox. I get error that it cannot find the table. The name is misspelled in the expression. There is an extra "i".

=DLookUp("[OMnummer]","[Administratievegegevens]", "ID=1")

[Missinglinq]

I get the 'zip file is empty' error, on the first download. The Dutch versions of Access apparently does use the semi-colon instead of a comma, and since the Table only has a single Record, all you really need is

=DLookUp("[OMnummer]";"Administratievegegevens")

The Table name, because it's a String, and in Quotes, doesn't need the square brackets; doesn't hurt, but isn't necessary, as it would be if you were talking about a Field Name.

As has been said, number is a Reserved Word, and a poor choice for a Field Name, but once again, it shouldn't be causing a problem, here...didn't when I tested it.

I'd try the above syntax, without the Criteria, and see what happens.

Linq ;0)>

[LadyL]

Ah that finally seems to work, thanks!