update table from combobox

[quasimodo]

Hi,

How do I update a table from a combobox?

I have a login form with a combo box (cboName) and a text box for password.
if a user selects a name from that combo, I want to update a table with that
name.

There are 2 tables:

Employee ( id_employee - autonumber
firstname - text
passw - text)

Orders (order_id.......
........
employee -----> from Employee table)

on login form I have text box, a combo box and a button with this:
Private Sub btnGo_Click()
If Me.txtPass.Value = DLookup ("passw", "Employee", "[id_employee]="
&Me.cboEmployee.Value) Then
strName = Me.cboEmployee.Column (1)

DoCmd.OpenForm "myform"
End If
End Sub

How do I update [employee] from Orders table with strName ?


Any help would be appreciated !

 

[Alex Dybenko]

Hi,
you can use update query:

currentdb.execute "Update Orders Set [employee]='" & strName & "' Where
order_id=" & lngOrderID, dbfailonerror

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[quasimodo]

Thank you for help, but I receive the following message "Run time error 3061.
Too few parameters. Expected 1."

And whem I run it in immediate window. I receive "Compile error. Argument
not optional"


What argument? What parameter?

 

[Paolo]

Hi Quasimodo,
try in this way
currentdb.execute "Update Orders Set [employee]=""" & strName & """ where
order_id=" & lngOrderID, dbfailonerror
If lngOrderID is not a numeric put it between quotations i.e.
currentdb.execute "Update Orders Set [employee]=""" & strName & """ where
order_id=""" & lngOrderID & """", dbfailonerror

HTH Paolo

Thank you for help, but I receive the following message "Run time error 3061.
Too few parameters. Expected 1."

And whem I run it in immediate window. I receive "Compile error. Argument
not optional"


What argument? What parameter?

Hi,
you can use update query:

currentdb.execute "Update Orders Set [employee]='" & strName & "' Where
order_id=" & lngOrderID, dbfailonerror

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[Paolo]

Hi Quasimodo,
try in this way
currentdb.execute "Update Orders Set [employee]=""" & strName & """ where
order_id=" & lngOrderID, dbfailonerror
If lngOrderID is not a numeric put it between quotations i.e.
currentdb.execute "Update Orders Set [employee]=""" & strName & """ where
order_id=""" & lngOrderID & """", dbfailonerror

HTH Paolo

Thank you for help, but I receive the following message "Run time error 3061.
Too few parameters. Expected 1."

And whem I run it in immediate window. I receive "Compile error. Argument
not optional"


What argument? What parameter?

Hi,
you can use update query:

currentdb.execute "Update Orders Set [employee]='" & strName & "' Where
order_id=" & lngOrderID, dbfailonerror

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[quasimodo]

Sorry, it was my mistake:
I want to INSERT value from combo box into table Orders, not to UPDATE.
Is it the same syntax?

Hi Quasimodo,
try in this way
currentdb.execute "Update Orders Set [employee]=""" & strName & """ where
order_id=" & lngOrderID, dbfailonerror
If lngOrderID is not a numeric put it between quotations i.e.
currentdb.execute "Update Orders Set [employee]=""" & strName & """ where
order_id=""" & lngOrderID & """", dbfailonerror

HTH Paolo

Thank you for help, but I receive the following message "Run time error 3061.
Too few parameters. Expected 1."

And whem I run it in immediate window. I receive "Compile error. Argument
not optional"


What argument? What parameter?

Hi,
you can use update query:

currentdb.execute "Update Orders Set [employee]='" & strName & "' Where
order_id=" & lngOrderID, dbfailonerror

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[Paolo]

To insert a new record the sintax is the following

CurrentDb.Execute ("INSERT INTO Orders ( employee ) SELECT """ & strName &
"""")

HTH Paolo

Sorry, it was my mistake:
I want to INSERT value from combo box into table Orders, not to UPDATE.
Is it the same syntax?

Hi Quasimodo,
try in this way
currentdb.execute "Update Orders Set [employee]=""" & strName & """ where
order_id=" & lngOrderID, dbfailonerror
If lngOrderID is not a numeric put it between quotations i.e.
currentdb.execute "Update Orders Set [employee]=""" & strName & """ where
order_id=""" & lngOrderID & """", dbfailonerror

HTH Paolo

Thank you for help, but I receive the following message "Run time error 3061.
Too few parameters. Expected 1."

And whem I run it in immediate window. I receive "Compile error. Argument
not optional"


What argument? What parameter?

:

Hi,
you can use update query:

currentdb.execute "Update Orders Set [employee]='" & strName & "' Where
order_id=" & lngOrderID, dbfailonerror

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[quasimodo]

Thank you, Paolo.
Probably I am doing something wrong because the record won't insert...!!!!
When I try to fill other text boxes in subformOrderDetails, it says "you
cannot add or change a record because a related record is required in table
'Employees' "

 

[Paolo]

That means you have some referential integrity between the table employee and
the table orders, so you can't insert in orders an employee that doesn't
exist in the table employee. I don't know what you are trying to do, but to
solve this problem ytou can add the employee also to the table
employee(before inserting in the table orders).

 

[quasimodo]

The employee DOES EXIST in table Employees !

In the login form (frmLogin) i have a combo box to select employees from
tblEmployees. The value I have selected in this combo box, I want to insert
it in tblOrders.

The tables are:
tblEmployees with id_employee --->autonumber (PK)
name -----> text
passw ------> text

tblOrders with order_id ------> text (PK)
order_date -----> date
.....................
employee --------> FK from tblEmployees
tblCustomer

The forms are:

frmLogin,
frmOrders with txtOrder_id,
txtOrder_date,..........cboCustomer,.......btnAddNewOrder

In tblEmployees, I have 2 records:
Let's say ( 1, John Dave, dave) and
(2, George Michael, michael)

the aplication starts up with frmLogin (it have a cboEmployee, txtPassw and
btnGo).
I select from cboEmployee 'John Dave', I type the password "dave" and press
btnGo.

Private Sub btnGo_Click()
if me.txtPassw.value = Dlookup(.......) then
strName = me.cboEmployee.column (1)
doCmd.Close
DoCmd.OpenForm "frmEmployees"
Else
Msgbox "invalid password
end if
End Sub

(In immediate window strName is 'John Dave')


In frmOrders I fill in the text boxes [order_id, order_date...........] , I
select customer from cboCustomer.

On After_update event I have the following code:
Private Sub cboCustomer_AfterUpdate()
currentDb.Execute(" INSERT INTO tblOrders(employee) SELECT """ & strName&
""")

End Sub

When I press btnAddNewOrder it says "you can not add or change a record
because a related record is required in table 'Employees'".

That means "John Dave" was not inserted in tblOrders.

 

[Paolo]

Well , the error raised make me think that exist a relationship between
tblEmployees and tblOrders but I don't think that such a relation would be
useful so if you have it, delete it.
Another suggestion: strName is declared as a global variable? you set it in
the login form and you use it in the orders form so if it isn't a global
variable the strName you use in orders form'll be void.

Cheers Paolo

The employee DOES EXIST in table Employees !

In the login form (frmLogin) i have a combo box to select employees from
tblEmployees. The value I have selected in this combo box, I want to insert
it in tblOrders.

The tables are:
tblEmployees with id_employee --->autonumber (PK)
name -----> text
passw ------> text

tblOrders with order_id ------> text (PK)
order_date -----> date
.....................
employee --------> FK from tblEmployees
tblCustomer

The forms are:

frmLogin,
frmOrders with txtOrder_id,
txtOrder_date,..........cboCustomer,.......btnAddNewOrder

In tblEmployees, I have 2 records:
Let's say ( 1, John Dave, dave) and
(2, George Michael, michael)

the aplication starts up with frmLogin (it have a cboEmployee, txtPassw and
btnGo).
I select from cboEmployee 'John Dave', I type the password "dave" and press
btnGo.

Private Sub btnGo_Click()
if me.txtPassw.value = Dlookup(.......) then
strName = me.cboEmployee.column (1)
doCmd.Close
DoCmd.OpenForm "frmEmployees"
Else
Msgbox "invalid password
end if
End Sub

(In immediate window strName is 'John Dave')


In frmOrders I fill in the text boxes [order_id, order_date...........] , I
select customer from cboCustomer.

On After_update event I have the following code:
Private Sub cboCustomer_AfterUpdate()
currentDb.Execute(" INSERT INTO tblOrders(employee) SELECT """ & strName&
""")

End Sub

When I press btnAddNewOrder it says "you can not add or change a record
because a related record is required in table 'Employees'".

That means "John Dave" was not inserted in tblOrders.

That means you have some referential integrity between the table employee and
the table orders, so you can't insert in orders an employee that doesn't
exist in the table employee. I don't know what you are trying to do, but to
solve this problem ytou can add the employee also to the table
employee(before inserting in the table orders).

 

[quasimodo]

strName is declared as a global variable in Module1 as follows:
Public strName As String

Why should I delete relationship between tblOrders and tblEmployees?
I am interested what particular order was set by an employee and by the end
of the month, how many orders was set by employees.

To be more specific: let's say my company sell candies. I have 2 employees :
Michael and John. I wanna know how many candies sold Michael and how many
candies sold John. Shouldn't I have a relationship between tblEmployees and
tblOrders?

Well , the error raised make me think that exist a relationship between
tblEmployees and tblOrders but I don't think that such a relation would be
useful so if you have it, delete it.
Another suggestion: strName is declared as a global variable? you set it in
the login form and you use it in the orders form so if it isn't a global
variable the strName you use in orders form'll be void.

Cheers Paolo

The employee DOES EXIST in table Employees !

In the login form (frmLogin) i have a combo box to select employees from
tblEmployees. The value I have selected in this combo box, I want to insert
it in tblOrders.

The tables are:
tblEmployees with id_employee --->autonumber (PK)
name -----> text
passw ------> text

tblOrders with order_id ------> text (PK)
order_date -----> date
.....................
employee --------> FK from tblEmployees
tblCustomer

The forms are:

frmLogin,
frmOrders with txtOrder_id,
txtOrder_date,..........cboCustomer,.......btnAddNewOrder

In tblEmployees, I have 2 records:
Let's say ( 1, John Dave, dave) and
(2, George Michael, michael)

the aplication starts up with frmLogin (it have a cboEmployee, txtPassw and
btnGo).
I select from cboEmployee 'John Dave', I type the password "dave" and press
btnGo.

Private Sub btnGo_Click()
if me.txtPassw.value = Dlookup(.......) then
strName = me.cboEmployee.column (1)
doCmd.Close
DoCmd.OpenForm "frmEmployees"
Else
Msgbox "invalid password
end if
End Sub

(In immediate window strName is 'John Dave')


In frmOrders I fill in the text boxes [order_id, order_date...........] , I
select customer from cboCustomer.

On After_update event I have the following code:
Private Sub cboCustomer_AfterUpdate()
currentDb.Execute(" INSERT INTO tblOrders(employee) SELECT """ & strName&
""")

End Sub

When I press btnAddNewOrder it says "you can not add or change a record
because a related record is required in table 'Employees'".

That means "John Dave" was not inserted in tblOrders.

That means you have some referential integrity between the table employee and
the table orders, so you can't insert in orders an employee that doesn't
exist in the table employee. I don't know what you are trying to do, but to
solve this problem ytou can add the employee also to the table
employee(before inserting in the table orders).

 

[Paolo]

Well, if you think that you need this relation (for me is unnecessary 'cause
you can do your monthly calculation in a query and bound the two table
there), at least set it as one to many (one record in tblemployees for many
records in tblorders). If you have a one to one relation as I suspect access
expct that for every record in tblOrders exist a record in tblEmployees.

Regards Paolo

strName is declared as a global variable in Module1 as follows:
Public strName As String

Why should I delete relationship between tblOrders and tblEmployees?
I am interested what particular order was set by an employee and by the end
of the month, how many orders was set by employees.

To be more specific: let's say my company sell candies. I have 2 employees :
Michael and John. I wanna know how many candies sold Michael and how many
candies sold John. Shouldn't I have a relationship between tblEmployees and
tblOrders?

Well , the error raised make me think that exist a relationship between
tblEmployees and tblOrders but I don't think that such a relation would be
useful so if you have it, delete it.
Another suggestion: strName is declared as a global variable? you set it in
the login form and you use it in the orders form so if it isn't a global
variable the strName you use in orders form'll be void.

Cheers Paolo

The employee DOES EXIST in table Employees !

In the login form (frmLogin) i have a combo box to select employees from
tblEmployees. The value I have selected in this combo box, I want to insert
it in tblOrders.

The tables are:
tblEmployees with id_employee --->autonumber (PK)
name -----> text
passw ------> text

tblOrders with order_id ------> text (PK)
order_date -----> date
.....................
employee --------> FK from tblEmployees
tblCustomer

The forms are:

frmLogin,
frmOrders with txtOrder_id,
txtOrder_date,..........cboCustomer,.......btnAddNewOrder

In tblEmployees, I have 2 records:
Let's say ( 1, John Dave, dave) and
(2, George Michael, michael)

the aplication starts up with frmLogin (it have a cboEmployee, txtPassw and
btnGo).
I select from cboEmployee 'John Dave', I type the password "dave" and press
btnGo.

Private Sub btnGo_Click()
if me.txtPassw.value = Dlookup(.......) then
strName = me.cboEmployee.column (1)
doCmd.Close
DoCmd.OpenForm "frmEmployees"
Else
Msgbox "invalid password
end if
End Sub

(In immediate window strName is 'John Dave')


In frmOrders I fill in the text boxes [order_id, order_date...........] , I
select customer from cboCustomer.

On After_update event I have the following code:
Private Sub cboCustomer_AfterUpdate()
currentDb.Execute(" INSERT INTO tblOrders(employee) SELECT """ & strName&
""")

End Sub

When I press btnAddNewOrder it says "you can not add or change a record
because a related record is required in table 'Employees'".

That means "John Dave" was not inserted in tblOrders.



:

That means you have some referential integrity between the table employee and
the table orders, so you can't insert in orders an employee that doesn't
exist in the table employee. I don't know what you are trying to do, but to
solve this problem ytou can add the employee also to the table
employee(before inserting in the table orders).

 

[quasimodo]

The relation is set as one to many, as you described below........

Well, if you think that you need this relation (for me is unnecessary 'cause
you can do your monthly calculation in a query and bound the two table
there), at least set it as one to many (one record in tblemployees for many
records in tblorders). If you have a one to one relation as I suspect access
expct that for every record in tblOrders exist a record in tblEmployees.

Regards Paolo

strName is declared as a global variable in Module1 as follows:
Public strName As String

Why should I delete relationship between tblOrders and tblEmployees?
I am interested what particular order was set by an employee and by the end
of the month, how many orders was set by employees.

To be more specific: let's say my company sell candies. I have 2 employees :
Michael and John. I wanna know how many candies sold Michael and how many
candies sold John. Shouldn't I have a relationship between tblEmployees and
tblOrders?

Well , the error raised make me think that exist a relationship between
tblEmployees and tblOrders but I don't think that such a relation would be
useful so if you have it, delete it.
Another suggestion: strName is declared as a global variable? you set it in
the login form and you use it in the orders form so if it isn't a global
variable the strName you use in orders form'll be void.

Cheers Paolo

:

The employee DOES EXIST in table Employees !

In the login form (frmLogin) i have a combo box to select employees from
tblEmployees. The value I have selected in this combo box, I want to insert
it in tblOrders.

The tables are:
tblEmployees with id_employee --->autonumber (PK)
name -----> text
passw ------> text

tblOrders with order_id ------> text (PK)
order_date -----> date
.....................
employee --------> FK from tblEmployees
tblCustomer

The forms are:

frmLogin,
frmOrders with txtOrder_id,
txtOrder_date,..........cboCustomer,.......btnAddNewOrder

In tblEmployees, I have 2 records:
Let's say ( 1, John Dave, dave) and
(2, George Michael, michael)

the aplication starts up with frmLogin (it have a cboEmployee, txtPassw and
btnGo).
I select from cboEmployee 'John Dave', I type the password "dave" and press
btnGo.

Private Sub btnGo_Click()
if me.txtPassw.value = Dlookup(.......) then
strName = me.cboEmployee.column (1)
doCmd.Close
DoCmd.OpenForm "frmEmployees"
Else
Msgbox "invalid password
end if
End Sub

(In immediate window strName is 'John Dave')


In frmOrders I fill in the text boxes [order_id, order_date...........] , I
select customer from cboCustomer.

On After_update event I have the following code:
Private Sub cboCustomer_AfterUpdate()
currentDb.Execute(" INSERT INTO tblOrders(employee) SELECT """ & strName&
""")

End Sub

When I press btnAddNewOrder it says "you can not add or change a record
because a related record is required in table 'Employees'".

That means "John Dave" was not inserted in tblOrders.



:

That means you have some referential integrity between the table employee and
the table orders, so you can't insert in orders an employee that doesn't
exist in the table employee. I don't know what you are trying to do, but to
solve this problem ytou can add the employee also to the table
employee(before inserting in the table orders).

 

[Paolo]

Delete it and try if in this way it works. Perhaps there's some something in
the relation that block the creation of a new record in tblorders.
I don't have further suggestion. Sorry

The relation is set as one to many, as you described below........

Well, if you think that you need this relation (for me is unnecessary 'cause
you can do your monthly calculation in a query and bound the two table
there), at least set it as one to many (one record in tblemployees for many
records in tblorders). If you have a one to one relation as I suspect access
expct that for every record in tblOrders exist a record in tblEmployees.

Regards Paolo

strName is declared as a global variable in Module1 as follows:
Public strName As String

Why should I delete relationship between tblOrders and tblEmployees?
I am interested what particular order was set by an employee and by the end
of the month, how many orders was set by employees.

To be more specific: let's say my company sell candies. I have 2 employees :
Michael and John. I wanna know how many candies sold Michael and how many
candies sold John. Shouldn't I have a relationship between tblEmployees and
tblOrders?

:

Well , the error raised make me think that exist a relationship between
tblEmployees and tblOrders but I don't think that such a relation would be
useful so if you have it, delete it.
Another suggestion: strName is declared as a global variable? you set it in
the login form and you use it in the orders form so if it isn't a global
variable the strName you use in orders form'll be void.

Cheers Paolo

:

The employee DOES EXIST in table Employees !

In the login form (frmLogin) i have a combo box to select employees from
tblEmployees. The value I have selected in this combo box, I want to insert
it in tblOrders.

The tables are:
tblEmployees with id_employee --->autonumber (PK)
name -----> text
passw ------> text

tblOrders with order_id ------> text (PK)
order_date -----> date
.....................
employee --------> FK from tblEmployees
tblCustomer

The forms are:

frmLogin,
frmOrders with txtOrder_id,
txtOrder_date,..........cboCustomer,.......btnAddNewOrder

In tblEmployees, I have 2 records:
Let's say ( 1, John Dave, dave) and
(2, George Michael, michael)

the aplication starts up with frmLogin (it have a cboEmployee, txtPassw and
btnGo).
I select from cboEmployee 'John Dave', I type the password "dave" and press
btnGo.

Private Sub btnGo_Click()
if me.txtPassw.value = Dlookup(.......) then
strName = me.cboEmployee.column (1)
doCmd.Close
DoCmd.OpenForm "frmEmployees"
Else
Msgbox "invalid password
end if
End Sub

(In immediate window strName is 'John Dave')


In frmOrders I fill in the text boxes [order_id, order_date...........] , I
select customer from cboCustomer.

On After_update event I have the following code:
Private Sub cboCustomer_AfterUpdate()
currentDb.Execute(" INSERT INTO tblOrders(employee) SELECT """ & strName&
""")

End Sub

When I press btnAddNewOrder it says "you can not add or change a record
because a related record is required in table 'Employees'".

That means "John Dave" was not inserted in tblOrders.



:

That means you have some referential integrity between the table employee and
the table orders, so you can't insert in orders an employee that doesn't
exist in the table employee. I don't know what you are trying to do, but to
solve this problem ytou can add the employee also to the table
employee(before inserting in the table orders).

 

[quasimodo]

I delete it. No errors but strName still isn't inserted into [employee] from
tblOrders.

Thank you very much for your patience and help!

 

[Paolo]

Last idea,
Is the recordsource of frmorder table order or a query that involves
employees?
The recordsource must be tblorder 'cause if you use a query with employees
an error like the one you described can be raised...

I delete it. No errors but strName still isn't inserted into [employee] from
tblOrders.

Thank you very much for your patience and help!

Delete it and try if in this way it works. Perhaps there's some something in
the relation that block the creation of a new record in tblorders.
I don't have further suggestion. Sorry

 

[quasimodo]

The recordsource is tblOrders

Last idea,
Is the recordsource of frmorder table order or a query that involves
employees?
The recordsource must be tblorder 'cause if you use a query with employees
an error like the one you described can be raised...

I delete it. No errors but strName still isn't inserted into [employee] from
tblOrders.

Thank you very much for your patience and help!

Delete it and try if in this way it works. Perhaps there's some something in
the relation that block the creation of a new record in tblorders.
I don't have further suggestion. Sorry