Solved

Check for matching record

Posted on 2013-06-07
8
355 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
The Orion Papers

Are you interested in becoming an AWS Certified Solutions Architect?

Discover a new interactive way of training for the exam.

 
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 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

Major Incident Management Communications

Major incidents and IT service outages cost companies millions. Often the solution to minimizing damage is automated communication. Find out more in our Major Incident Management Communications infographic.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

707 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