combo box + enter key

hi,

I would like to know that is that possible for me to key in a data in combo box and after I press "enter", if the data is in the access table, then, it will retrieve and display the records in other combo box and text box that are related to that data in the same table.
but if the data is not in the access table, then it will add the data in the access table by asking the user to key in other data that are related.

how can I write the code for this function?
s_lingAsked:
Who is Participating?
 
Valliappan ANConnect With a Mentor Senior Tech ConsultantCommented:
Private Sub Combo1_KeyPress(KeyAscii As Integer)
  If KeyAscii <> vbKeyReturn Then
     Exit Sub
  End If
  If Combo1.Text = "" Then
   MsgBox "You must key in the PEC Ref. Number."
   Exit Sub
  End If
 
 With DatPecref.Recordset
     .FindFirst "PECREFNO='" & Replace(Combo1.Text,"'","''") & "'"
     If .NoMatch = False Then
        'Found - Display it.
        cmdOk.Enabled = False
        MsgBox "data displayed"
        DatPecref.Refresh
     Else
       'Not Found - Allow user to enter data.
       CmdOk.Enabled = True
     End If
  End With
        SQL = "SELECT INVDATE,BLKNO,BLKAREA,STRIPNO,STRIPLEN,TEAM,REMARK,ATTACH FROM INVFILE WHERE
PECREFNO
= '" & Combo1.Text & "'"
       DatInvfile.RecordSource = SQL
       DatInvfile.Refresh
End Sub

Private Sub cmdOK_Click()
        DatPecref.Recordset.AddNew
        DatPecref.Recordset.Fields("PECREFNO").Value = Combo1
        DatPecref.Recordset.Fields("ACCOUNT").Value = Combo2
        DatPecref.Recordset.Fields("LISENS1").Value = Combo3
        DatPecref.Recordset.Fields("MPLAN1").Value = txtmanagement
        DatPecref.Recordset.Fields("MPLAN2").Value = txtPlan
        DatPecref.Recordset.Fields("ISSUDATE").Value = txtIssue
        DatPecref.Update
End Sub

-------
Hope this will help you. I have not run the program. So, if you get any error, let me try to help you. I dont think this method will be suitable for you, if you need to give entry option in DBGrid also. Then, your DBGrid entries will be saved and not the PECREF data, when the user does not press OK and quits your program or presses CANCEL.

You need to get to know about Transactions to do this kind of updations, also, you need to use Recordsets or direct updation with Database object, instead of using data controls, as far as I understand. You might need to use something like:

Workspaces(0).BeginTrans

before, the user, starts to enter data to the grid type data. And if the user presses the OK, then you will need to commit the data like,

Workspaces(0).CommitTrans

If the user presses Cancel, then, like this:

Workspaces(0).Rollback

You could also learn about ADO, which is the latest Microsoft technology for database operations, instead of using DAO. But DAO is good for Access.

Cheers.
0
 
Ryan ChongCommented:
Hi s_ling, you can use the SQL like this:

"Select fields1, fields2 from table where fields1 = '" & cmobo1.text & "'"

if NoRecordWasFound then
   prompt use to CreateNewRecord
else
   ShowExactRecord
endif

'The way to use depends on what method you use..
0
 
s_lingAuthor Commented:
hi ryancys,

can you give me an example?
isn't that select function is for dbgrid?

here is my code
 If Combo1.Text = "" Then
    MsgBox "you must key in the data"
    DatPecref.Recordset.Edit
    DatPecref.Recordset.CancelUpdate
    End If
   
 
   With DatPecref.Recordset
        .FindFirst "PECREFNO='" & Combo1 & "'"
    If .NoMatch = True Then
        GoTo NotFound
    Else
        GoTo FoundIt
    End If
   
   
NotFound:

    DatPecref.Recordset.Update
    cmdOk.Enabled = False
    MsgBox "data saved"
    DatPecref.Refresh
    DBGRID.Enabled = True

   
FoundIt:
    DatPecref.Recordset.CancelUpdate
    DatPecref.Recordset.Fields("PECREFNO").Value = Combo1
    DatPecref.Recordset.Fields("ACCOUNT").Value = Combo2
    DatPecref.Recordset.Fields("LISENS1").Value = Combo3
    DatPecref.Recordset.Fields("MPLAN1").Value = txtmanagement
    DatPecref.Recordset.Fields("MPLAN2").Value = txtPlan
    DatPecref.Recordset.Fields("ISSUDATE").Value = txtIssue
    SQL = "SELECT INVDATE,BLKNO,BLKAREA,STRIPNO,STRIPLEN,TEAM,REMARK,ATTACH FROM INVFILE WHERE PECREFNO = '" & Combo1.Text & "'"
       DatInvfile.RecordSource = SQL
       DatInvfile.Refresh
   
   
  End With
  End If
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

 
s_lingAuthor Commented:
the
SQL = "SELECT INVDATE,BLKNO,BLKAREA,STRIPNO,STRIPLEN,TEAM,REMARK,ATTACH FROM INVFILE WHERE PECREFNO = '" & Combo1.Text & "'"
      DatInvfile.RecordSource = SQL
      DatInvfile.Refresh

is for the dbgrid below the table with combo box and text box.

   
   
 
0
 
Ryan ChongCommented:
Hi s_ling,

What control/ technology are you using? Data Control?

Are the dbgrid and textboxes are bounded?

In my code, try to use "Exit Sub" before the labels.
0
 
s_lingAuthor Commented:
hi ryancys ,

I'm using DAO.

the dbgrid is bounded butta the text boxes and combo boxes are manual.

0
 
Valliappan ANSenior Tech ConsultantCommented:
s_ling,

Are you asking for checking for the Enter key?

If so, you could use the KeyPress event of the combo box, and check if ENTER key is pressed. Then check if the entry in Combo box exists, if exists, then display the details or else, go to Add mode.

Like this:

Private Sub Combo1_KeyPress(KeyAscii As Integer)
If KeyAscii = vbKeyReturn Then
    'check if record exist here.
    ':
   
    'if record exist then
        'display data.
    'else
        'go to add mode.
    'end if
   
End If
End Sub

Cheers
0
 
s_lingAuthor Commented:
hi, valli_an

there is an error when I type in your code.
is it I need to declare the add mode in sub or function?
0
 
Ryan ChongCommented:
Hi s_ling,

use in Private Sub Combo1_KeyDown() ..
0
 
Valliappan ANSenior Tech ConsultantCommented:
s_ling,

its just a model i gave, with comments. You could see a single quote at the start of those lines, like the add mode and all. Ok, you could give a try like this(I have used your code, with some changes. also i have avoided using goto statements):

--------
Private Sub Combo1_KeyPress(KeyAscii As Integer)
   If KeyAscii <> vbKeyReturn Then
      Exit Sub
   End If
   If Combo1.Text = "" Then
    MsgBox "you must key in the data"
    DatPecref.Recordset.Edit
    DatPecref.Recordset.CancelUpdate
    Exit Sub
   End If
   
  With DatPecref.Recordset
      .FindFirst "PECREFNO='" & Combo1 & "'"
      If .NoMatch = True Then
          'Not Found
         DatPecref.Recordset.Update
         cmdOk.Enabled = False
         MsgBox "data saved"
         DatPecref.Refresh
        DBGRID.Enabled = True
      Else
        'Found It
         DatPecref.Recordset.CancelUpdate
         DatPecref.Recordset.Fields("PECREFNO").Value = Combo1
         DatPecref.Recordset.Fields("ACCOUNT").Value = Combo2
         DatPecref.Recordset.Fields("LISENS1").Value = Combo3
         DatPecref.Recordset.Fields("MPLAN1").Value = txtmanagement
         DatPecref.Recordset.Fields("MPLAN2").Value = txtPlan
         DatPecref.Recordset.Fields("ISSUDATE").Value = txtIssue
         SQL = "SELECT INVDATE,BLKNO,BLKAREA,STRIPNO,STRIPLEN,TEAM,REMARK,ATTACH FROM INVFILE WHERE PECREFNO
= '" & Combo1.Text & "'"
        DatInvfile.RecordSource = SQL
        DatInvfile.Refresh
   End If    
End Sub
-------------
0
 
s_lingAuthor Commented:
hi valli_an ,

there is an error while compile
this code :
"DatPecref.Recordset.CancelUpdate"

run time error '3020'
Update or CancelUpdate without AddNew or Edit


0
 
Valliappan ANSenior Tech ConsultantCommented:
hi, I just paste your code, with some modifications, since you wanted to do something when the user presses ENTER key in Combo box.

Actually what are you trying to do, when the Combo1 Text record is found, and when the record is not found?

Because, in the code you have posted, when not found, it does Update and when found, it does some assigning values to data control.

Cheers.
0
 
s_lingAuthor Commented:
hi valli_an,

thanx for be so patience.
actually what I was trying to do is ....
after user press the command Add, it will set focus to Combo1 among the Combo boxes and text boxes.

user must key in the PECno in Combo1 and press ENTER. if the data is in the access table "PECREFNO". then, it will display the records that are related including the DBGRID in the other access table.

if not, the user must keep on key in the other data such as AccountNo, Linsensee, Management....and save the data using command button OK.  
0
 
Ryan ChongCommented:
Hi s_ling,

You need something like this:

Private Sub Combo1_KeyPress(KeyAscii As Integer)
    If KeyAscii <> vbKeyReturn Then Exit Sub
    SQL = "SELECT INVDATE,BLKNO,BLKAREA,STRIPNO,STRIPLEN,TEAM,REMARK,ATTACH FROM INVFILE WHERE PECREFNO  = '" _
            & Replace(Combo1.Text, "'", "''") & "'"
   
    DatPecref.RecordSource = SQL
    DatPecref.Refresh
   
    If DatPecref.Recordset.RecordCount > 0 Then
        'Record Match
        Set DBGrid1.DataSource = DatPecref
        DBGrid1.Refresh
    Else
        'No
       
        'if not, the user must keep on key in the other data such as AccountNo, Linsensee, Management....and
        'save the data using command button OK.
       
        'DatPecref.Recordset.AddNew
        'DatPecref.Recordset.Fields!PECREFNO = Combo1
        '.
        '.
       
        'DatPecref.Recordset.Update
       
    End If
End Sub

'Hope will help.
0
 
s_lingAuthor Commented:
Hi valli_an,

there is a run-time error "3426" (action was cancelled by an associated object) in this code
       .FindFirst "PECREFNO='" & Replace                 (Combo1.Text, "'", "''") & "'"

actually what's wrong with this? is it there is something wrong with my other coding?



0
 
s_lingAuthor Commented:
HI ryancys ,

there is an error in this code:
Set DBGRID.DataSource = DatPecref

the error is "run-time error 430 - class does not support automation or does not support expected interface"
0
 
Ryan ChongCommented:
Hi s_ling, what grid control you using?

DataGrid or DBGrid? You use Data Control , right?
0
 
Ryan ChongCommented:
Hi s_ling,

remove the code: Set DBGRID.DataSource = DatPecref

goto DBGrid Control Property and set the DataSource. It will do the same things.
0
 
Valliappan ANSenior Tech ConsultantCommented:
You might need to do this before FindFirst like this:


If Not (.BOF Or .EOF) Then
      .MoveFirst
      .FindFirst "PECREFNO='" & Replace                 (Combo1.Text, "'", "''") & "'"
End If

Also, read this article:
http://www.vb-faq.com/Articles/Chang/NoBoundControls.asp
0
 
s_lingAuthor Commented:
valli_an & ryancys,

thanx for the help !!! butttttt...
I'm still facing the same problems
0
 
Valliappan ANSenior Tech ConsultantCommented:
s_ling, could you send your code. Let me have a look at it later, and respond to you.

To My email id: valli_an@hotpop.com
Cheers.
0
 
s_lingAuthor Commented:
valli_an,

I cannnot send my email to you because your mail box is unknown to the mail-system.
have you type in the right id?
0
 
s_lingAuthor Commented:
ryancys ,

I cannot set the data source of DBGrid to datpecref because the records that I want to display is in the datinvfile.

the records in datpecref is for the table above the DBGrid.
0
 
Ryan ChongCommented:
Hi s_ling, are you rush to this project? If don't then you can email it to me and i will check this later. Thanks.

(I'm using VB6 and Access 2000)

my email is ryancys78@yahoo.com
0
 
Valliappan ANSenior Tech ConsultantCommented:
o, sorry, yes that one was wrong, i have one in hotpop, though. but you could send it to valli_an@softhome.net.

Also, if possible, some details, and your database. :)
0
 
s_lingAuthor Commented:
ryancys ,

I'm not in rush
actually this is a project for my industrial training in a company...
I'm using VB6 and Access 97.

thanx :-)


0
 
s_lingAuthor Commented:
thanx!valli_an

I have sent you my project...
0
 
Valliappan ANSenior Tech ConsultantCommented:
s_ling, to which id did you send the project?
0
 
s_lingAuthor Commented:
valli_an@softhome.net.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.