Solved Not sure what error this is

[asteropi]

First of, let me say this community is amazing. You have helped me solve one problem after another and really helped me learn, so I'm grateful for that.

Now onto the next and hopefully final one.

I'm trying to create a list of my products with an add button that will be unconnected from the order products list so that they will not update if I make any changes for historical reasons.
See below


The greek labels read, product name, colour, size, price. But it's only the labels,
But when I try to run the command I get this error

But then, even though it adds the proper product, it marks the Τιμή (Price) field which is a Dlookup to a query with an IF command. As you can see there is no |1 in the line in VBA so I'm stumped again

Please help!

 

[CarlettoFed]

We can see how yesterday I remain waiting for your call.

 

[asteropi]

I'm free I can call now

 

[asteropi]

here's the new database

 

[CarlettoFed]

Close AnyDesk, reopen it and try calling again.

 

[asteropi]

Close AnyDesk, reopen it and try calling again.

My connection is bad. I'll call you in couple of hours when I get to the office

 

[Edgar_]

You have controls with names that have greek characters in OrderDetailsF. Try to use the english alphabet for the controls, but follow the instructions in this post by @sonic8, confirmed by @MajP:

What should work if my hypothesis is correct:

1. Export using SaveAsText
2. Remove/replace all non-ANSI characters in control names
3. Remove/replace the corresponding characters in the VBA code
4. Import the files with LoadFromText.

However, do this for all your forms. The SaveAsText and LoadFromText methods look basically the same, SaveAsText creates the file, LoadFromText imports the created file.

Hope that helps

Note: If you compile, you'll find a bunch of problems in other parts of your code, try to solve those too. You can also redo the form making sure no controls have those "foreign" (to Access) characters.

 

[asteropi]

@Edgar_ Thank you
I basically redid all my database but only left a couple of controls in Greek. I have changed them now but still getting the same error
I will try the solution you gave me and post here again

 

[CarlettoFed]

You need to change the line

Price = Nz(DLookup([Price], [OrderDetailsQ], "ProductDetailsID=" & Me.ProductCmb), 0)

in the following

Price = Nz(DLookup("[Price]", "[OrderDetailsQ]", "ProductDetailsID=" & Me.ProductCmb), 0)

 

[cheekybuddha]

DLookup() expects the fieldname you wish to lookup as its first parameter, the 'domain' (table or query name) as its second parameter, and the criteria if required as the third parameter.

You appear to be passing a field or control as the first two parameters. My guess is that you need to surround them with double-quotes instead of square brackets.

As you have it, you are passing the value held in the current field of your first parameter (rather than the field name itself), and there is no corresponding control or field called OrdersDetailQ, hence the error you received.

 

[asteropi]

DLookup() expects the fieldname you wish to lookup as its first parameter, the 'domain' (table or query name) as its second parameter, and the criteria if required as the third parameter.

You appear to be passing a field or control as the first two parameters. My guess is that you need to surround them with double-quotes instead of square brackets.

As you have it, you are passing the value held in the current field of your first parameter (rather than the field name itself), and there is no corresponding control or field called OrdersDetailQ, hence the error you received.

Fixed.
I'm still confused about when it's supposed to be brackets or "
Thank you for the input

 

[cheekybuddha]

I'm still confused about when it's supposed to be brackets or "

I'm short on time right now - will try and give an explanation later if no-one else does so before

 

[CarlettoFed]

Read here https://support.microsoft.com/en-us/office/dlookup-function-8896cb03-e31f-45d1-86db-bed10dca5937

 

[Gasman]

You would use [] when you have spaces in your object names (field, tables, queries... you get the idea ) which is not recommended

So if you have a field called "Net Price" then you need "[Net Price]", however if you make it "NetPrice" then no square brackets are required.

I believe you can also use it to differentiate between a fieldname and a control called the same, but not 100% on that.

 

[The_Doc_Man]

I'm still confused about when it's supposed to be brackets or "

Fair question, and actually easy to answer, but I need to bring in some reading materials. I picked the DCount function for my example, but this technique will work for other functions as well. FIRST, look up the Access context of what you want to use. I said "DCount" was my example so my web search was "DCount Function in Access." That led me to this:

DCount Function - Microsoft Support

support.microsoft.com

Reading this, you quickly come to the Syntax section, which shows you the call arguments. For DCount, they are Expr, Domain, and Criteria. If you look in the specific explanations for each of those arguments, they suggest that they are normally STRING items. Expr and Domain are normally a field name and the name of something that produces a recordset. The only exception for Expr is that instead of being a field name, it could be a formula involving a field in the given domain. The Domain argument MUST be a named table or query; it can't be an SQL query string.

Because you want strings for the DCount function, you would most likely need the quotes to delimit the strings that are your arguments. In theory it would be possible to use variables that contained STRINGs that name viable names for the Expr and Domain, and that also supply a valid string that would make sense if tacked onto a WHERE clause.

As to brackets, understand that Access "parses" (picks apart) your inputs. It breaks the input apart according to English rules of language, so that means that a space character is a natural parsing delimiter (separator). If you have a field in your table called Part Number (including the space) then if Access sees Part followed by a space, it thinks that the name Part stands along and it has no clue what to do with Number, but to Access that would look like like you left out an arithmetic operator (e.g. Part + Number, TWO item names in an badly formed expression). Both VBA and SQL do this the same way. Therefore, when you really mean Part Number to be the entire name INCLUDING THAT SPACE, you need [] to surround it. A lot of special characters would have the same effect. Which is why we often say "don't use special characters in names."

The need for [] and "" are independent of each other. The rules that would demand either can occur simultaneously, as in fact they do for the case of the Domain Aggregate functions. Those specials can deeply confuse the VBA or SQL parsers that are looking for field or variable names.

 

[asteropi]

@The_Doc_Man Thank you for the input.
I learned early on not to include spaces so that's not an issue and Microsoft support is always the frst option I look at and try to solve it myself before I ask here. It's a slow learning curve I'm afraid

 

[The_Doc_Man]

It's a slow learning curve I'm afraid

On this point, we completely agree. I remember when I started with Access 97 that it took a while for me to think "relational" as opposed to "flat file." But it IS doable. Hang in there.

 

[Gasman]

For what you have created so far, you are doing well.
I know of someone who has been using Access for over 19 years, and probably could not do what you have done , so keep up the good work.
At least you are prepared to look for yourself first, unlike a lot of new users these days.

 

[Pat Hartman]

I believe you can also use it to differentiate between a fieldname and a control called the same

You are asking for serious trouble if you have a control name of MyField either unbound or bound to a field named MyOtherField and a control named MyOtherField but either unbound or bound to a field named MyField2.

Here's a database that shows a few of the bad things that happen when you use bad control or field names

Useful code AND the effects of using bad column names

This database has several useful code procedures that you can copy into your BE to fix up defaults you don't like: 1. Change the allow ZLS string property to No If you want to make a text field required, it makes no sense at all to have the Allow ZLS property set to Yes. 2. Turn off sub...

www.access-programmers.co.uk

 

[Edgar_]

@Edgar_ Thank you
I basically redid all my database but only left a couple of controls in Greek. I have changed them now but still getting the same error
I will try the solution you gave me and post here again

If the solution above does not work, try redoing the database again. I know it won't be fun, but make sure you stick to the following rules:
1. ABCDEFGHIJKLMNOPQRSTUVWXYZ, abcdefghijklmnopqrstuvwxyz, 0123456789 and "_" are the only characters you have to use for all names across everything including forms, reports, controls inside them, tables, fields and vba code. Every single time you have to name something, stick to those characters.
2. Avoid reserved words as names.
3. Use Option Explicit at the top of all your modules. You can enable that in the VBA IDE options. It will help you catch many issues before you run code or after trying to compile.
4. Compile regularly to catch these problems before they become unmanageable.
5. Back up your progress regularly to avoid losing your work when the file becomes corrupt. It WILL become corrupt at some point.
6. Access assigns default names to objects, always override them with unique names. An easy way to test why this is important is doing this:

Private Sub Form_Load()
    MsgBox TypeName(Me.SomeName)
End Sub

Where "SomeName" refers to a control that has the same name as the field it is bound to. Open the form to read the message. Now rename the control to something else and open the form again to read the message. It might be the same code, but it will first evaluate as control, then as field.

I don't know what else to add at the moment. Good luck.