Hi azuki,

you can retrieve all staffid from database add then into arraylist , then you compare staffid from GUI with each item in arraylist. I assume that GUI password in textbox1.text then

Dim staffIDcolection As ArrayList = New ArrayList

Dim ss1 As String = ""

Dim i As Integer = 0

Dim conn As New OleDbConnection(connString)

Dim strsql As String = " SELECT staffID FROM stafftable "

Dim cmdgrid As OleDbCommand = New OleDbCommand(strsql, conn)

conn.Open()

Try

Dim sdrgrid As OleDbDataReader = cmdgrid.ExecuteReader

With sdrgrid

If .HasRows Then

While .Read

ss1 = sdrgrid.Item(0).ToString

staffIDcolection.Insert(i, ss1)

i = i + 1

End While

End If

.Close()

End With

i = 0

Catch exc As Exception

MsgBox(exc.Message)

Exit Sub

Finally

conn.Close()

End Try

For i = 0 To staffIDcolection.Count - 1

if textbox1.text = staffIDcolection(i).tostring then

" what ever you want to do"

end if

 

jamesnguyen_anewtech

I have a similar situation where I have an Access form and an attached subform.  I have a combo box on the main form, and a datasheet in the subform, with a field the same as the combo box.  I would like to click in the datasheet field to update/add new record and have it populate the field with the same value that is visible in the main form's combo box.  It is working, except for a combo box value which has no records yet in the datasheet (based on two different tables and two different queries).  The fields are linked by Master and Child links in the form properties.  So, when selecting a value from the combo box (which has no corresponding records in the datasheet/subform), the combo box returns a null value.  Then, if selecting the same selected value in the datasheet field, the error states that that the sub or function is not defined at "Sub field1_Click(), likely because I started the VB script from the Expression Builder's OnClick event for field1, but assume I need to keep this in the script as it is related to this function.  Help greatly appreciated.  This is what I have so far (Mainform = frmMain, field to copy to = field1):

Option Compare Database

Dim rcd As DAO.Recordset, db As DAO.Database
Sub field1_Click()
Set db = CurrentDb

Set rcd = dbOpenRecordset("field1", dbOpenDynaset, dbAppendOnly)

With rcd
'Start a new record
.AddNew

'Set the field values
rcd![field1] = Forms!frmMain!Combo1
.Update
    
    On Error GoTo 0
   
Macro1_Exit:
    Exit Sub

End Sub

 

Barry

B&W Pantex

At first I created a successful macro to do this function, but doesn't work for null values in the combo box.  I have tried relating the tables, joining them in queries (plural because I had one query with only the combo box field and another querty with multiple fields for the datasheet).  I now have them linked in the form (with the combo box being the Parent and the datasheet as the Child).  For some reason, when selecting a value in the combo box, which does not yet have a record for in the datasheet-based table, the selection changes to a null value following it's selection.  It returns an error on the values discussed above, due to the fact that they are null, stating that cannot enter a null value.  I have broken all links in queries, emptied the OnClick event completely, and the combo box still reacts the same.  Anybody ever had this issue?

Barry

B&W Pantex

I don't code in Access but here's a couple suggestions:

1.  Check for a null value here...
IIF(Forms!frmMain!Combo1 IS NOTHING,0,Forms!frmMain!Combo1 
OR
IIF(Forms!frmMain!Combo1.SelectedIndex = -1,yourDefaultValue,Forms!frmMain!Combo1.SelectedValue)
  

OR

2.  Set the field in the DB to allow nulls

try this one if you are using vb.net..

change your combobox into textbox.Then add another two listboxes.Name it lstuser and lstpass.

Then this will be your code:

Codes in Upper part (Global Part)

Public Class Form_

Dim numberofrow,index as Integer

Dim dataset as New Dataset

Dim adapter as New OleDb.OleDbDataAdapter



codes in FormLoad:

dim con as new oledb.oleDbConnection

Dim sql as String="Select * From your_database_table_name"

adapter=New OleDb.OleDbDataAdapter(sql,con)



con.ConnectionString="Provider=Microsoft.Jet.OleDb.4.0;Data Source=Location_of_your_database/database_name.mdb"



con.Open()

adapter.Fill(dataset,"setfill")

con.close()



numberofrow=dataset.Tables("setfill").Rows.Count



For index=0 To numberofrow-1

lstuser.Items.Add(dataset.Tables("setfill").Rows(index).Item(0))

lstpass.Items.Add(dataset.Tables("setfill").Rows(index).Item(1))







CODES IN BUTTON LOGIN:

dim f2 as new Form2

dim indexuser,indexpass as integer



indexuser=lstuser.FindStringExact(txtuser.Text)

indexpass=lstpass.FindStringExact(txtpassText)



if txtuser.Text="" or txtpass.Text="" Then

msgbox("Null value is invalid!")

txtuser.Focus()

elseif indexuser<0 or indexpass<0 Then

msgbox("User not found!")

txtuser.Text=""

txtpass.Text=""

txtuser.Focus()

elseif indexuser=indexpass Then

f2.show()

me.Hide()

else

msgbox("Wrong login!")

end if