Solved

Check for matching record

Posted on 2013-06-07
8
349 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

730 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