Solved

Check for matching record

Posted on 2013-06-07
8
337 Views
Last Modified: 2013-06-26
Microsoft access forms

After I enter data in the field [barcode] of frm_lunch, I want to see if the value of mid([barcode],9,7) exists in [tbl_idnum].[grade].  

If it does, I want to return to input on frm_lunch.

If it does not, I want to open form [pufrm_enterid] and pass the value of [mid([barcode],9,7) to [tbl_idnum].[grade]. and ask for input to field [tbl_idnum].[full name].
0
Comment
Question by:TIgerV
  • 4
  • 3
8 Comments
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 39230057
Can you upload a sample db???

ET
0
 
LVL 39

Expert Comment

by:als315
ID: 39230073
Will be better if you upload sample DB.
You can check existence with dcount:
If dcount("*","tbl_idnum","[grade]="&mid(Me.barcode,9,7)) =0 then
            docmd.openform "purfrm_enterid",acNormal,,,acFormAdd
end if
on form purfrm_enterid you can set default value for field grade to your mid(...)
and do not allow empty field for full name field.
0
 

Author Comment

by:TIgerV
ID: 39230103
sample
BSC.accdb
0
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
LVL 39

Expert Comment

by:als315
ID: 39231216
I don't clear understand your idea, but you should have some logic to move to a new record after each scan and validation. It was not added.
Test sample.
Button's code check field Scan Data and form for adding new record is opened if Grade is missing in existing records.
BSC.accdb
0
 

Author Comment

by:TIgerV
ID: 39231720
That is GREAT!  
The scanner usually inserts an "ENTER" for next record after.
How do I Tell the function to go to next record when it goes back to the input form?
0
 
LVL 39

Expert Comment

by:als315
ID: 39231944
I can't check it with scanner, but it should work
BSC-1.accdb
0
 

Author Comment

by:TIgerV
ID: 39233152
That stops taking data after 3 characters.
Can we just tell the code to return to the form and go to next record?
0
 
LVL 39

Accepted Solution

by:
als315 earned 500 total points
ID: 39233179
Code is in unload event on form:
Forms!frm_lunch.SetFocus
DoCmd.GoToRecord , , acNewRec

May be there are other special characters? Try to debug sub:
Private Sub Scan_data_KeyDown(KeyCode As Integer, Shift As Integer)
Dim SC As String
If KeyCode < 32 Then
    KeyCode = 0
Set breakpoint to line KeyCode = 0 and look at KeyCode value
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question