Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Check for matching record

Posted on 2013-06-07
8
Medium Priority
?
367 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 19

Expert Comment

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

ET
0
 
LVL 40

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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 40

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 40

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 40

Accepted Solution

by:
als315 earned 2000 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

610 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