Using data from one table & storing in another table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,
I posted a question last week, but havnt yet been able to find any help.
Might be to do with how im explaing what i want to do, but i hope someone can
help.

I have a database which uses comand buttons on a form to input data into
text boxes. I have two tables set up. One of which holds information i want
to be displayed on my form when a relivant button is clicked, and the other
table is where i would like the finished information to be stored when all my
form fields are complete.

Table1 - this will be my 'reference' data table and have: Barcode, Product
and Weight in the table - 3 colums with the relivant info manually inputted
into each column.
Table2 - this will have: Barcode, Product, Weight, but also: date, time,
quantity, and an 'autonumber' automatically generated when a new record is
added.

When a barcode is inputted into my form, the Product and Weight will
automatically appear in other text boxes, as this data will be taken from
Table1. Then the user will input (into other text boxes) the Date, Time and
Quantity and the record will be allocated an 'autonumber' when the 'add new
record' comand button is clicked.
Then i want all of this info to be stored as a new record in Table2. I dont
want to add records to Table1 as this is my 'product reference' table.

I hope this makes sence and that someone can help me.

Many thanks in advance.

Nick
 
How do you select the record you want from Table1?
Once you have selected it, it is just a matter of populating the controls on
your form that are bound to the fields in Table2.
You might consider a multi column combo box to select a record from Table1,
then use the combo's After Update event to populate the controls from the
combo's columns.
 
Hi,
I have it all set up using text boxes and comand buttons. This is how it
must be done as it needs to be very user friendly and will eventually be used
on a touch screen monitor - so dont really want to use a combo box.

The way i select a record: The forms record source is set to Table1. The
operator inputs the product barcode into the first text box (this is
unbound), then presses the 'Find' command button.
The 'product' and 'size' which relates to the barcode is then displayed in
the next two text boxes as the control source for these is set to these
fields in Table1.

Then using comand buttons, they input the quantity, date and time into other
text boxes. This information, along with the above, i want all to be stored
in Table2, however cant seem to be able to do this, as the forms record
source is set to Table1 in order to carry out the 'find' process as above.

My info in table1 will always stay the same. Its just table2 which i want
to be able to add the records to.

I hope this makes sence. Im a beginner with big ambitions, so some step by
step help in leymans terms would be really helpful.

Thanks so much.
Nick
 
The form's record source should be Table2, Not Table1.
A combo box is about as friendly a control as there is. It was specifically
designed for looking things up.

If you insist on using text boxes and command buttons, then you should still
be doing a search on Table 1 and and populating the controls from the result
of that search. But, this takes a lot more code.
 
Hi,
Thanks for the help - really appreciated.
I will try the combo box, however would you be able to give me an idea of
how the code should go if I use comand buttons & text boxes??
Its just that there will be lots of products listed in my text box,
therefore this is why i would prefer to search a table. My combo box would
probably have more than 150 choices in it, so i would prefer users to scan a
barcode & press 'find'.

Hope you can guide me in the right direction.

Many thanks
Nick
 
Okay, didn't know about the scanning part before. In this case, a command
button would be just fine.

So, after the barcode has been scanned in, the Click event of the command
button can lookup the record in Table1 and populate the fields. The text box
where the barcode is scanned into should not be a bound control. It would
look something like this (use real names, of course):

Private Sub cmdScanCode_Click()
Dim rst As DAO.Recordset

Set rst = Currentdb.OpenRecordset("Table1", dbOpenDynaset)
With rst
.FindFirst "[BarCode] = '" & Me.txtBarCode & "'"
If .NoMatch Then
MsgBox "BarCode " & Me.txtBarCode & " Not Found in Table1"
Else
Me.SomeControl = !SomeControl
Me.AnotherControl = !AnotherField
Me.AnyOldControl = !AnyOldField
End If

.Close
End With
Set rst = Nothing
End Sub
 
Hi,
Thanks for the info - now i think i may get somewhere.
My code is now as follows:

Private Sub Find_Click()
Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset("Product List", dbOpenDynaset)
With rst
.FindFirst "[Barcode] = '" & Me.txt_BarCode & "'"
If .NoMatch Then
MsgBox "Barcode " & Me.Barcode & " Not Found in Product List"
Else
Me.Barcode = !Barcode
Me.Product = !Product
Me.Outer_Format = !Weight
End If

.Close
End With
Set rst = Nothing
End Sub

However - Where above it says - '" & Me.txt_BarCode & "'" this gets
highlighted and i get a 'Compile Error - Method or data member not found'
when i click my 'find' button.

Your help is greatly appreciated.
Thanks

Klatuu said:
Okay, didn't know about the scanning part before. In this case, a command
button would be just fine.

So, after the barcode has been scanned in, the Click event of the command
button can lookup the record in Table1 and populate the fields. The text box
where the barcode is scanned into should not be a bound control. It would
look something like this (use real names, of course):

Private Sub cmdScanCode_Click()
Dim rst As DAO.Recordset

Set rst = Currentdb.OpenRecordset("Table1", dbOpenDynaset)
With rst
.FindFirst "[BarCode] = '" & Me.txtBarCode & "'"
If .NoMatch Then
MsgBox "BarCode " & Me.txtBarCode & " Not Found in Table1"
Else
Me.SomeControl = !SomeControl
Me.AnotherControl = !AnotherField
Me.AnyOldControl = !AnyOldField
End If

.Close
End With
Set rst = Nothing
End Sub
--
Dave Hargis, Microsoft Access MVP


Nick T said:
Hi,
Thanks for the help - really appreciated.
I will try the combo box, however would you be able to give me an idea of
how the code should go if I use comand buttons & text boxes??
Its just that there will be lots of products listed in my text box,
therefore this is why i would prefer to search a table. My combo box would
probably have more than 150 choices in it, so i would prefer users to scan a
barcode & press 'find'.

Hope you can guide me in the right direction.

Many thanks
Nick
Click to expand...
Click to expand...
 
First thing that makes me suspicious is you have two different names that
should actually be the same. Are you sure the name is txt_barcode or is it
barcode?

The other thing that could cause this is if the code is not in the form's
module. If that is the case, you have to qualify the name of the control:
Forms!FormName!txt_barcode.

But, my guess is you really meant Me.barcode
--
Dave Hargis, Microsoft Access MVP


Nick T said:
Hi,
Thanks for the info - now i think i may get somewhere.
My code is now as follows:

Private Sub Find_Click()
Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset("Product List", dbOpenDynaset)
With rst
.FindFirst "[Barcode] = '" & Me.txt_BarCode & "'"
If .NoMatch Then
MsgBox "Barcode " & Me.Barcode & " Not Found in Product List"
Else
Me.Barcode = !Barcode
Me.Product = !Product
Me.Outer_Format = !Weight
End If

.Close
End With
Set rst = Nothing
End Sub

However - Where above it says - '" & Me.txt_BarCode & "'" this gets
highlighted and i get a 'Compile Error - Method or data member not found'
when i click my 'find' button.

Your help is greatly appreciated.
Thanks

Klatuu said:
Okay, didn't know about the scanning part before. In this case, a command
button would be just fine.

So, after the barcode has been scanned in, the Click event of the command
button can lookup the record in Table1 and populate the fields. The text box
where the barcode is scanned into should not be a bound control. It would
look something like this (use real names, of course):

Private Sub cmdScanCode_Click()
Dim rst As DAO.Recordset

Set rst = Currentdb.OpenRecordset("Table1", dbOpenDynaset)
With rst
.FindFirst "[BarCode] = '" & Me.txtBarCode & "'"
If .NoMatch Then
MsgBox "BarCode " & Me.txtBarCode & " Not Found in Table1"
Else
Me.SomeControl = !SomeControl
Me.AnotherControl = !AnotherField
Me.AnyOldControl = !AnyOldField
End If

.Close
End With
Set rst = Nothing
End Sub
--
Dave Hargis, Microsoft Access MVP


Nick T said:
Hi,
Thanks for the help - really appreciated.
I will try the combo box, however would you be able to give me an idea of
how the code should go if I use comand buttons & text boxes??
Its just that there will be lots of products listed in my text box,
therefore this is why i would prefer to search a table. My combo box would
probably have more than 150 choices in it, so i would prefer users to scan a
barcode & press 'find'.

Hope you can guide me in the right direction.

Many thanks
Nick

:

The form's record source should be Table2, Not Table1.
A combo box is about as friendly a control as there is. It was specifically
designed for looking things up.

If you insist on using text boxes and command buttons, then you should still
be doing a search on Table 1 and and populating the controls from the result
of that search. But, this takes a lot more code.
--
Dave Hargis, Microsoft Access MVP


:

Hi,
I have it all set up using text boxes and comand buttons. This is how it
must be done as it needs to be very user friendly and will eventually be used
on a touch screen monitor - so dont really want to use a combo box.

The way i select a record: The forms record source is set to Table1. The
operator inputs the product barcode into the first text box (this is
unbound), then presses the 'Find' command button.
The 'product' and 'size' which relates to the barcode is then displayed in
the next two text boxes as the control source for these is set to these
fields in Table1.

Then using comand buttons, they input the quantity, date and time into other
text boxes. This information, along with the above, i want all to be stored
in Table2, however cant seem to be able to do this, as the forms record
source is set to Table1 in order to carry out the 'find' process as above.

My info in table1 will always stay the same. Its just table2 which i want
to be able to add the records to.

I hope this makes sence. Im a beginner with big ambitions, so some step by
step help in leymans terms would be really helpful.

Thanks so much.
Nick


:

How do you select the record you want from Table1?
Once you have selected it, it is just a matter of populating the controls on
your form that are bound to the fields in Table2.
You might consider a multi column combo box to select a record from Table1,
then use the combo's After Update event to populate the controls from the
combo's columns.


--
Dave Hargis, Microsoft Access MVP


:

Hi,
I posted a question last week, but havnt yet been able to find any help.
Might be to do with how im explaing what i want to do, but i hope someone can
help.

I have a database which uses comand buttons on a form to input data into
text boxes. I have two tables set up. One of which holds information i want
to be displayed on my form when a relivant button is clicked, and the other
table is where i would like the finished information to be stored when all my
form fields are complete.

Table1 - this will be my 'reference' data table and have: Barcode, Product
and Weight in the table - 3 colums with the relivant info manually inputted
into each column.
Table2 - this will have: Barcode, Product, Weight, but also: date, time,
quantity, and an 'autonumber' automatically generated when a new record is
added.

When a barcode is inputted into my form, the Product and Weight will
automatically appear in other text boxes, as this data will be taken from
Table1. Then the user will input (into other text boxes) the Date, Time and
Quantity and the record will be allocated an 'autonumber' when the 'add new
record' comand button is clicked.
Then i want all of this info to be stored as a new record in Table2. I dont
want to add records to Table1 as this is my 'product reference' table.

I hope this makes sence and that someone can help me.

Many thanks in advance.

Nick
Click to expand...
Click to expand...
Click to expand...
 
Hi Dave,

Bingo! Thank you so much for your help!!!
Its so good when somthing finally goes right after so many frustrating late
nights!

I changed the code to:

..FindFirst "[Barcode] = " & Me.Barcode

Thanks again, but im sure ill be on again some time!

Nick
Klatuu said:
First thing that makes me suspicious is you have two different names that
should actually be the same. Are you sure the name is txt_barcode or is it
barcode?

The other thing that could cause this is if the code is not in the form's
module. If that is the case, you have to qualify the name of the control:
Forms!FormName!txt_barcode.

But, my guess is you really meant Me.barcode
--
Dave Hargis, Microsoft Access MVP


Nick T said:
Hi,
Thanks for the info - now i think i may get somewhere.
My code is now as follows:

Private Sub Find_Click()
Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset("Product List", dbOpenDynaset)
With rst
.FindFirst "[Barcode] = '" & Me.txt_BarCode & "'"
If .NoMatch Then
MsgBox "Barcode " & Me.Barcode & " Not Found in Product List"
Else
Me.Barcode = !Barcode
Me.Product = !Product
Me.Outer_Format = !Weight
End If

.Close
End With
Set rst = Nothing
End Sub

However - Where above it says - '" & Me.txt_BarCode & "'" this gets
highlighted and i get a 'Compile Error - Method or data member not found'
when i click my 'find' button.

Your help is greatly appreciated.
Thanks

Klatuu said:
Okay, didn't know about the scanning part before. In this case, a command
button would be just fine.

So, after the barcode has been scanned in, the Click event of the command
button can lookup the record in Table1 and populate the fields. The text box
where the barcode is scanned into should not be a bound control. It would
look something like this (use real names, of course):

Private Sub cmdScanCode_Click()
Dim rst As DAO.Recordset

Set rst = Currentdb.OpenRecordset("Table1", dbOpenDynaset)
With rst
.FindFirst "[BarCode] = '" & Me.txtBarCode & "'"
If .NoMatch Then
MsgBox "BarCode " & Me.txtBarCode & " Not Found in Table1"
Else
Me.SomeControl = !SomeControl
Me.AnotherControl = !AnotherField
Me.AnyOldControl = !AnyOldField
End If

.Close
End With
Set rst = Nothing
End Sub
--
Dave Hargis, Microsoft Access MVP


:

Hi,
Thanks for the help - really appreciated.
I will try the combo box, however would you be able to give me an idea of
how the code should go if I use comand buttons & text boxes??
Its just that there will be lots of products listed in my text box,
therefore this is why i would prefer to search a table. My combo box would
probably have more than 150 choices in it, so i would prefer users to scan a
barcode & press 'find'.

Hope you can guide me in the right direction.

Many thanks
Nick

:

The form's record source should be Table2, Not Table1.
A combo box is about as friendly a control as there is. It was specifically
designed for looking things up.

If you insist on using text boxes and command buttons, then you should still
be doing a search on Table 1 and and populating the controls from the result
of that search. But, this takes a lot more code.
--
Dave Hargis, Microsoft Access MVP


:

Hi,
I have it all set up using text boxes and comand buttons. This is how it
must be done as it needs to be very user friendly and will eventually be used
on a touch screen monitor - so dont really want to use a combo box.

The way i select a record: The forms record source is set to Table1. The
operator inputs the product barcode into the first text box (this is
unbound), then presses the 'Find' command button.
The 'product' and 'size' which relates to the barcode is then displayed in
the next two text boxes as the control source for these is set to these
fields in Table1.

Then using comand buttons, they input the quantity, date and time into other
text boxes. This information, along with the above, i want all to be stored
in Table2, however cant seem to be able to do this, as the forms record
source is set to Table1 in order to carry out the 'find' process as above.

My info in table1 will always stay the same. Its just table2 which i want
to be able to add the records to.

I hope this makes sence. Im a beginner with big ambitions, so some step by
step help in leymans terms would be really helpful.

Thanks so much.
Nick


:

How do you select the record you want from Table1?
Once you have selected it, it is just a matter of populating the controls on
your form that are bound to the fields in Table2.
You might consider a multi column combo box to select a record from Table1,
then use the combo's After Update event to populate the controls from the
combo's columns.


--
Dave Hargis, Microsoft Access MVP


:

Hi,
I posted a question last week, but havnt yet been able to find any help.
Might be to do with how im explaing what i want to do, but i hope someone can
help.

I have a database which uses comand buttons on a form to input data into
text boxes. I have two tables set up. One of which holds information i want
to be displayed on my form when a relivant button is clicked, and the other
table is where i would like the finished information to be stored when all my
form fields are complete.

Table1 - this will be my 'reference' data table and have: Barcode, Product
and Weight in the table - 3 colums with the relivant info manually inputted
into each column.
Table2 - this will have: Barcode, Product, Weight, but also: date, time,
quantity, and an 'autonumber' automatically generated when a new record is
added.

When a barcode is inputted into my form, the Product and Weight will
automatically appear in other text boxes, as this data will be taken from
Table1. Then the user will input (into other text boxes) the Date, Time and
Quantity and the record will be allocated an 'autonumber' when the 'add new
record' comand button is clicked.
Then i want all of this info to be stored as a new record in Table2. I dont
want to add records to Table1 as this is my 'product reference' table.

I hope this makes sence and that someone can help me.

Many thanks in advance.

Nick
Click to expand...
Click to expand...
Click to expand...
 
Glad I could help.
--
Dave Hargis, Microsoft Access MVP


Nick T said:
Hi Dave,

Bingo! Thank you so much for your help!!!
Its so good when somthing finally goes right after so many frustrating late
nights!

I changed the code to:

.FindFirst "[Barcode] = " & Me.Barcode

Thanks again, but im sure ill be on again some time!

Nick
Klatuu said:
First thing that makes me suspicious is you have two different names that
should actually be the same. Are you sure the name is txt_barcode or is it
barcode?

The other thing that could cause this is if the code is not in the form's
module. If that is the case, you have to qualify the name of the control:
Forms!FormName!txt_barcode.

But, my guess is you really meant Me.barcode
--
Dave Hargis, Microsoft Access MVP


Nick T said:
Hi,
Thanks for the info - now i think i may get somewhere.
My code is now as follows:

Private Sub Find_Click()
Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset("Product List", dbOpenDynaset)
With rst
.FindFirst "[Barcode] = '" & Me.txt_BarCode & "'"
If .NoMatch Then
MsgBox "Barcode " & Me.Barcode & " Not Found in Product List"
Else
Me.Barcode = !Barcode
Me.Product = !Product
Me.Outer_Format = !Weight
End If

.Close
End With
Set rst = Nothing
End Sub

However - Where above it says - '" & Me.txt_BarCode & "'" this gets
highlighted and i get a 'Compile Error - Method or data member not found'
when i click my 'find' button.

Your help is greatly appreciated.
Thanks

:

Okay, didn't know about the scanning part before. In this case, a command
button would be just fine.

So, after the barcode has been scanned in, the Click event of the command
button can lookup the record in Table1 and populate the fields. The text box
where the barcode is scanned into should not be a bound control. It would
look something like this (use real names, of course):

Private Sub cmdScanCode_Click()
Dim rst As DAO.Recordset

Set rst = Currentdb.OpenRecordset("Table1", dbOpenDynaset)
With rst
.FindFirst "[BarCode] = '" & Me.txtBarCode & "'"
If .NoMatch Then
MsgBox "BarCode " & Me.txtBarCode & " Not Found in Table1"
Else
Me.SomeControl = !SomeControl
Me.AnotherControl = !AnotherField
Me.AnyOldControl = !AnyOldField
End If

.Close
End With
Set rst = Nothing
End Sub
--
Dave Hargis, Microsoft Access MVP


:

Hi,
Thanks for the help - really appreciated.
I will try the combo box, however would you be able to give me an idea of
how the code should go if I use comand buttons & text boxes??
Its just that there will be lots of products listed in my text box,
therefore this is why i would prefer to search a table. My combo box would
probably have more than 150 choices in it, so i would prefer users to scan a
barcode & press 'find'.

Hope you can guide me in the right direction.

Many thanks
Nick

:

The form's record source should be Table2, Not Table1.
A combo box is about as friendly a control as there is. It was specifically
designed for looking things up.

If you insist on using text boxes and command buttons, then you should still
be doing a search on Table 1 and and populating the controls from the result
of that search. But, this takes a lot more code.
--
Dave Hargis, Microsoft Access MVP


:

Hi,
I have it all set up using text boxes and comand buttons. This is how it
must be done as it needs to be very user friendly and will eventually be used
on a touch screen monitor - so dont really want to use a combo box.

The way i select a record: The forms record source is set to Table1. The
operator inputs the product barcode into the first text box (this is
unbound), then presses the 'Find' command button.
The 'product' and 'size' which relates to the barcode is then displayed in
the next two text boxes as the control source for these is set to these
fields in Table1.

Then using comand buttons, they input the quantity, date and time into other
text boxes. This information, along with the above, i want all to be stored
in Table2, however cant seem to be able to do this, as the forms record
source is set to Table1 in order to carry out the 'find' process as above.

My info in table1 will always stay the same. Its just table2 which i want
to be able to add the records to.

I hope this makes sence. Im a beginner with big ambitions, so some step by
step help in leymans terms would be really helpful.

Thanks so much.
Nick


:

How do you select the record you want from Table1?
Once you have selected it, it is just a matter of populating the controls on
your form that are bound to the fields in Table2.
You might consider a multi column combo box to select a record from Table1,
then use the combo's After Update event to populate the controls from the
combo's columns.


--
Dave Hargis, Microsoft Access MVP


:

Hi,
I posted a question last week, but havnt yet been able to find any help.
Might be to do with how im explaing what i want to do, but i hope someone can
help.

I have a database which uses comand buttons on a form to input data into
text boxes. I have two tables set up. One of which holds information i want
to be displayed on my form when a relivant button is clicked, and the other
table is where i would like the finished information to be stored when all my
form fields are complete.

Table1 - this will be my 'reference' data table and have: Barcode, Product
and Weight in the table - 3 colums with the relivant info manually inputted
into each column.
Table2 - this will have: Barcode, Product, Weight, but also: date, time,
quantity, and an 'autonumber' automatically generated when a new record is
added.

When a barcode is inputted into my form, the Product and Weight will
automatically appear in other text boxes, as this data will be taken from
Table1. Then the user will input (into other text boxes) the Date, Time and
Quantity and the record will be allocated an 'autonumber' when the 'add new
record' comand button is clicked.
Then i want all of this info to be stored as a new record in Table2. I dont
want to add records to Table1 as this is my 'product reference' table.

I hope this makes sence and that someone can help me.

Many thanks in advance.

Nick
Click to expand...
Click to expand...
Click to expand...
 
Back
Top