Data Valivdation from a table (1 Viewer)

[dcmphi]

I Have one Table named of "User" with following Column:
ID as PK Autonumber and Name, And a form following with text box
ID and Name All text box is unbound. when ID text box is updated with User's table id value its shows the name against the entered ID in the Unbound Name text box which ID is already exist in “User” table. If entered ID is not valid means not exist in table “User” then the Unbound Name text box Shows “Invalid User or My error message” and cursor setfocus to ID text box.

Example: Data already Exist in User table is

ID Name
1 Debashish Mahonta
2 Shajib Mahonta
3 Banna Das

When I enter 1 or 2 or 3 in form ID text box and press enter, the unbound text box shows the name "Debashish Mahonta for "Shajib Mahonta for 2" and "Banna Das for 3". But If I enter 0 or 4 or 5 or other value that not exist in user table then the unbound Name text box shows “invalid User” or My custom message and cursor setfocus to ID text box. How can I do it.

 

[sneuberg]

You could do what you described with the code that follows which is from the on click event of a button on a form in the attached database. Please note that the field name for name in this is UserName. If you use "name" as a field name you will end up with problems as nearly every object has a property named "name". You will find that that this button event code runs when the enter key is pressed. This happens because I changed the Default property of the button to Yes. You can find the Default property in the Other tab of the properties.

Private Sub Enter_Click()

Dim varUserName As Variant
If Me.ID & vbNullString <> vbNullString Then
    varUserName = DLookup("[UserName]", "[TheNameOfTheTable]", "[ID] = " & Me.ID)
    If IsNull(varUserName) Then
        Me.UserName = "Invalid User"
        Me.ID.SetFocus
    Else
        Me.UserName = varUserName
    End If
Else
    MsgBox "Please enter an ID"
    Me.ID.SetFocus
End If

End Sub

 

[dcmphi]

Thank you for your reply. See my attach file boss. In that db project i have a customer, Nominee, Transaction, TransactionType, VoucherType table. And i have frmTransaction form which is linked transaction table to store data in transaction table. To add a transaction by the form i want to validate data if the customer is already exist in customer table by ACNo. After that i want to validate Transtype, VoucherType then balance as like as bank software. When i create a transaction it checks ACNo, TransType, VoucherType, Balance(for debit transaction), Last Transaction date (if has previous transaction), Transaction will be completed if all data is valid otherwise not. And after a successful transaction i want to show a transactionID (Auto number field in transaction table). How to do it? if you have a sample db like which i want please share that db with me.

 

[MarkK]

You can run code for every keystroke in a textbox using the Textbox.Change event. Also, you can open a recordset to see if the record exists and get the username. Using these two facts, you could write code like...

private sub txtID_Change()
   const SQL as string = "SELECT * FROM YourTable WHERE ID = "

   me.username = ""
   if isnumeric(me.txtID.text) then
      with currentdb.openrecordset(SQL & me.txtID.text)
         if not .eof then  me.username = !Username
         .close
      end with
   end if
end sub

 

[dcmphi]

Thanks MarkK. It works. Can you help me how to show a table data in a message box. Example: I have a table Transaction. The table has a field named TransactionID which is Auto Number. When i add a record in the transaction table by a form after record added successfully it shows a msgbox with the last TransactionID and debit or credit amount against form inputed ACNo from transaction table. Can i explained?

 

[MarkK]

To get data out of a table you can open a recordset using a SQL statement, like...

   const SQL as string = "SELECT Field1 FROM Transaction WHERE TransactionID = "
   dim rst as dao.recordset

   set rst = currentdb.openrecordset(sql & me.someID)
   with rst
      if not .eof then msgbox !Field1, vbinformation
      .close
   end with

Is that what you mean?

 

[dcmphi]

Thanks MarkK. But how to use your code. Please explain it.

 

[MarkK]

Can you help me how to show a table data in a message box.

The code I posted shows how you can show table data in a message box. Please explain what part of it you don't understand.

 

[dcmphi]

When i want to add a record by a form in the table one record increased with autonumber. Example: my table named user and that has two fields: One is ID and two is Name and have two records.
ID Name
1 Debashish Mahonta
2 Shajib Mahonta

When i add a record by a form one ID number is increased to table User.

ID Name
3 Banna Das

I want to show the last record ID number (3) and Name (Banna Das) when record successfully added in Message box.

Example Message box:
Record successfully added.
ID: 3 Name: Banna Das

Thats all. Could i explain you?

 

[MarkK]

sub ShowLastUserAdded()
   const SQL as string = _
      "SELECT TOP 1 ID, Name " & _
      "FROM User " & _
      "ORDER BY ID Desc;"

   with currentdb.openrecordset(SQL)
      If Not .eof then MsgBox "User " & !ID & " " & !Name, vbInformation, "Successfully Added"
      .close
   end with
end sub

 

[dcmphi]

error on "_" underscore vb not recognized when i type the code.