code for no match in Subform based query

[smercer]

Urgent!! code for no match in Subform based query

Hi all

I am now trying to make a books sales form. When the user scans a book, I have VBA to copy data to a table tempoarly from another subform (called "sfrm_Current_Sales_For_Book_Des") based on query for record.

I have another subform (called "sfrm_Current_Sales_Book_Description_Count")based on a query to check to make sure that the user has not scaned the book already, if there is no match then it will enter the data, if the user has scaned it then a message will appear and won't enter any data. (I am doing two, one for book copy (books that have same title, author etc, and one for as described, which is to be coded.)

here is my code:

   If Me!sfrm_Current_Sales_Book_Description_Count.nomatch Then
        ' if there is no book copies (only One ISBN Number) already scanned then
        'This section is for the copying the sfrm_Current_Sales_For_Book_Des data over to sales form
        sfrm_Current_Sales_For_Book_Des!ISBN_Number = sfrm_Current_Sales_Book_Description!ISBN_Number
        sfrm_Current_Sales_For_Book_Des!Title = sfrm_Current_Sales_Book_Description!Title
        sfrm_Current_Sales_For_Book_Des!Author_Surname = sfrm_Current_Sales_Book_Description!Author_Surname
        sfrm_Current_Sales_For_Book_Des!Author_FName = sfrm_Current_Sales_Book_Description!Author_FName
        sfrm_Current_Sales_For_Book_Des!Series = sfrm_Current_Sales_Book_Description!Series
        sfrm_Current_Sales_For_Book_Des!Category = sfrm_Current_Sales_Book_Description!Category
        sfrm_Current_Sales_For_Book_Des!Sub_Category = sfrm_Current_Sales_Book_Description!Sub_Category
        
        
        'and now for the each record in the Each_Book Subform
        sfrm_Current_Sales_For_Book_Des.SetFocus
        sfrm_Current_Sales_For_Book_Des!sfrm_Current_Sales_For_Each_Book_Datasheet_View.SetFocus
        DoCmd.RunCommand acCmdRecordsGoToNew
        sfrm_Current_Sales_For_Book_Des!sfrm_Current_Sales_For_Each_Book_Datasheet_View!Book_ID = sfrm_Current_Sales_Book_Description!sfrm_Each_Book_Sales_Selected!Book_ID
        sfrm_Current_Sales_For_Book_Des!sfrm_Current_Sales_For_Each_Book_Datasheet_View!ISBN_Number = sfrm_Current_Sales_Book_Description!sfrm_Each_Book_Sales_Selected!ISBN_Number
        sfrm_Current_Sales_For_Book_Des!sfrm_Current_Sales_For_Each_Book_Datasheet_View!Cover = sfrm_Current_Sales_Book_Description!sfrm_Each_Book_Sales_Selected!Cover
        sfrm_Current_Sales_For_Book_Des!sfrm_Current_Sales_For_Each_Book_Datasheet_View!Cost_Price = sfrm_Current_Sales_Book_Description!sfrm_Each_Book_Sales_Selected!Cost_Price
        sfrm_Current_Sales_For_Book_Des!sfrm_Current_Sales_For_Each_Book_Datasheet_View!Sale_Price = sfrm_Current_Sales_Book_Description!sfrm_Each_Book_Sales_Selected!Sale_Price

    Else

as you can see it is quite long so I have omited the rest. I have tried the code with:

If Me!sfrm_Current_Sales_Book_Description_Count!CountOfISBN_Number.Value >= 1 Then

after the else and that works.

the problem is when there is no match in the form, the text boxes are not visable, and code does not work
so does any one have any suggestions for the .nomatch?

Thanks in advance

 

[robart6]

code for no match in subform based query

I'm not very experienced but here's what I have to offer. Would it help to use
If Me!sfrm_Current_Sales_Book_Description_Count!Count OfISBN_Number.Value <1 Then
instead of
If Me!sfrm_Current_Sales_Book_Description_Count.nomatch Then

Please let me know if it helped. If it doesn't help, please provide detailed logic flow of what you want to happen and I'll take another shot at it.

 

[smercer]

Would it help to use
If Me!sfrm_Current_Sales_Book_Description_Count!Count OfISBN_Number.Value <1 Then

Please let me know if it helped. If it doesn't help, please provide detailed logic flow of what you want to happen and I'll take another shot at it.

No I does not work. I get the same result (see Attachment)

I tried to post the database here but it is 169kb zipped (maximum for forum is 100kb). can email instead if you like.

 

[KenHigg]

Are you still having problems with this?

 

[smercer]

Are you still having problems with this?

yes, I am. I always say I have fixed it if I have. I try to be considerate to other people who may be working on it that do not know I have fixed it.

Thanks for replying Ken

 

[KenHigg]

Have you thought of using a dlookup() to see if the scan occured?

 

[smercer]

Have you thought of using a dlookup() to see if the scan occured?

I have never used dlookup() before. How would I do that?

Thanks for replying ken

 

[KenHigg]

You're in for a treat. There are several of these type functions refered to as 'Domain' functions. The vb help cover them pretty good. Goes something like:

DLookup(expr, domain, [criteria])

or

DLookup("[field_two]", "table_one" , "[field_one] = 'x'") then

This will return the contents of field_one if the criteria,[field_one] = 'x', is true

Let me know if you get this to work...

 

[smercer]

I should have said earlier that, in order for my code to work, my code makes the text box change focus from "Book_ID_Lookup" to "Book_ID_Lookup_2" and back again. I am using OnTimer() event for this to so that if a value was entered it would check it each time. I need to make the result appear as soon as the user scans a book and not need to press enter or tab all the time.

So question is: Can I incorporate an "OR" into the Dlookup()? or is it best to use an if statement instead.

P.S when you mentioned DLookup, I immediately had a look at the help file and is indeed as you say.

Thanks very much for your help. Your very clever!!

 

[KenHigg]

Sure, use a compound expression in the dlookup() function. All you're looking for is a true condition.

 

[smercer]

Sure, use a compound expression in the dlookup() function. All you're looking for is a true condition.

thanks for taking the time to talk to me.

I do not know what you mean by "Compound expression". I am new to Visual basic.

Thanks again.

 

[KenHigg]

'Can I incorporate an "OR" into the Dlookup()?'

Sure:

DLookup("[field_two]", "table_one" , "([field_one] = 'x') or ([field_three] = 'y') ")


One more note:

DLookup(expr, domain, [criteria])

As long as '[criteria]' comes back as true, you should get the value in 'expr'. If is false, you should get a null which may need special consideration.

 

[smercer]

I have got one dlookup working. but I have another problem I have to Iron out before attemting the next one.

I will just see if I can handle it myself (This is the best way for me to learn)

 

[Rich]

You should be aware that Domain aggregate functions are slow, and the OpenRecordset method is usually much faster

 

[smercer]

You should be aware that Domain aggregate functions are slow, and the OpenRecordset method is usually much faster

if your talking about looking up records in numbers of 10,000 it properbly would make it slow, however the customer is never going to buy that many books.

the table that the data is going into is a Temporary, and at the end of the sale, the records will be moved over to another table called "tbl_Day_Sales_For_Each_Book" and another table I have not created yet.

Thanks for your opinon Rich

 

[Rich]

Thanks for your opinon Rich

It's not an opinion, in fact even Microsoft admit they're slow, even providing alternative codes for one or two of them


Why are you moving records around anyway, you only need to store the PK from Books in the Customer sales table?

 

[smercer]

Thanks all for helping especially KenHigg.

I have now fixed the problem and now I am on to a new problem, so I will post a new thread called: Urgent - find record using criteria from variable.

Thanks again!!

Rich: I did not mean to insult your Intelligence (You are quite brainy), I was just saying thanks