Write value from a cell into multiple records

[Lol999]

Hi, I hope this is in the right part of the forum, so apologies if not.
I'm working on a simple database to manage the location of powertools within a company. One of the forms I need to design has the following design:
Users enter a location into a text box (it will be via barcode reader but that's separate to the issue here).
The curser will then move to the first empty record in a table that contains the following fields:
Location, Part Code and Date.

The location will be derived from the text box, the part code will be entered by the user (again barcode), and the date will be set to the days date by default.
I need to write each record into the table as it is entered into the table. Is it SQL/VBA or a combination of the two I'm looking at?
Many thanks, Lol

 

[isladogs]

Set the form record source to the table containing those fields

Enter the data for the new record.
Is it a single record form or continuous?

When you move away from the new record, the data will automatically be saved. You can add a save button to do this if you wish

 

[pbaldy]

So the part code is being entered into a bound textbox on a new record? For the date you can simply set the default value to Date(). If you're doing multiple records, you'll probably need to pull the location in the before insert or before update events:

Me.BoundLocationTextbox = Me.UnboundInputTextbox

Personally I wouldn't use a bound form for this (normally I would), I'd use VBA to validate entry and insert the data programmatically.

 

[Lol999]

Thanks for the replies. Frustratingly I can't bring to mind the exact methodology I would use but the algorithm would look something like this:

Form opens and sets focus to text box
Enter Location into text box
Focus changes to table displayed in form.
Event onUpdate puts value of text box into the Location field, date set to todays by default, Part code entered by barcode scanner
Next record selected, previous saved automatically.

Any resources for VBA syntax would be appreciated
Cheers, Lol

 

[The_Doc_Man]

Start with whether you want to use bound or unbound forms to do this.

With bound forms, you need to worry about intercepting an "implied update" when someone tries to move to another record, but your "next record selected, previous record saved automatically" is automatically true. With unbound forms, you need to do more work behind the scenes but you have no worries about accidentally updating a record with an implied update because you cannot navigate an unbound form.

For a bound form, your update event would put whatever is in the relevant fields into the underlying record. For an unbound form, you won't have an OnUpdate event because you can't update an unbound form. But you CAN have a SAVE button that you would click to open a recordset to the selected place, diddle the fields around, and save/close the recordset.

For EITHER type of form, you can have a drop-down box (probably a Combo Box) that you could use to verify the value put in via your barcode scanner. For a bound form, there is a combo box wizard that can be told to move to the correct record given the key for that record - which will probably come from your barcode. For an unbound form, it is trickier because you might have to look up the record on your own, probably best done with a recordset since you might want to update the recordset anyway.

All of this depends on you first deciding what you want to do and how you want to approach the problem. Any answers we give you would depend on your choices first.

 

[Lol999]

Hi DocMan, I think after some playing around I'm going to go for the unbound option. I have worked with record sets and connections in the murky past so it's starting to ring a few bells.

What I am trying to accomplish is a simple form with a text box at the top which receives the location input from the barcode.
Below this will be a table to receive the part code which is scanned in via barcode. The table will be set to about 30 rows, so it would be a matter of putting a WHILE/WEND in to work through the table until a null or zero value is found writing a fresh record into the relevant table each iteration.

Hope this makes sense!

 

[Lol999]

On reflection I'm not sure how I'm going to make this work advancing the cursor through the grid.

Bit more thinking required.

 

[The_Doc_Man]

The table to receive the part code is perhaps part of your solution. Your next step is to decide how you are going to build the table. Because once you build the table, it becomes TRIVIAL to build an SQL string that will simultaneously (or at least, it will LOOK like it was simultaneously) jam the desired value in ALL of the related records.

Once you know how to build the list, the rest is relatively easy.

 

[pbaldy]

With an unbound form you wouldn't worry about advancing through the grid. Use the after update event of the part code textbox. Do any validation, insert the record and put focus back on the textbox ready for another scan. If desired you could display the records added in a subform.

 

[Lol999]

Okay, I think I have some of it sorted. I decided to create another table, called Tbl_Display and embedded this in a subform.
The main form has a simple text box, Txt_Location, for entry of the location tools are to be booked to.
Tbl_Display is linked into the table Tbl_Tools on a many to one basis.

Upon entering the location in the text box the first record gains focus and awaits data entry. Once entered a text box whose data source is Description from the table Tbl_Tools completes, and I have managed to get the focus to move to next record on hitting the enter key.
I intend to place a button on the main form and copy the tool ID entries from Tbl_Display and enter them into the table which manages the bookings, Tbl_Bookings. I need however to pass the value of the text box into the Location field of Tbl_Bookings alongside every tool ID entry.

After battling several hours I give up, I can't get the syntax right at all let alone the method so if someone can put this fool out of his misery.......

 

[pbaldy]

You can refer to the textbox in your append query to copy the records.

 

[Lol999]

You see I was going to use the On-Click event for a button and do it in a mix of VBA andSQL. The textbox variable is the sticking point here, I just can't figure out how to include it in an SQL statement :banghead:

 

[pbaldy]

INSERT INTO TargetTableName (Field1, Location)
SELECT FieldName, Forms!FormName.TextboxName
FROM SourceTableName

 

[Lol999]

First of all - IS THAT IT?!

Secondly, thank you I have been going round in circles!

I'll give it a try and let you know what happens


Cheers, Lol

 

[pbaldy]

Yes, that's it (for 1 field from table and another from textbox). Post back if you're still stuck.

 

[Lol999]

This is what I have:
INSERT INTO Tbl_Location (ID, Location)
SELECT ID, Forms!Frm_Booking.Txt_Location
FROM Tbl_Display

But the ID keeps causing a compile error with a Expected:case statement.

Am I missing some de-limiters or some other some such?

 

[pbaldy]

That should work fine as a saved query. If you have it in VBA like that, it will need to be tweaked. If that's the case, what's your full code?

 

[Lol999]

This is what I have so far, getting error 361 though

Private Sub Command7_Click()
Dim strSQL As String
strSQL = "INSERT INTO Tbl_Location (ID, Location) SELECT ID, Forms!Frm_Booking.Txt_Location FROM Tbl_Display"
CurrentDb.Execute strSQL
End Sub

 

[pbaldy]

The Execute method can't resolve form references. Try:

    strSql = "INSERT INTO Tbl_Location (ID, Location) " _
           & "SELECT ID, '" & Forms!Frm_Booking.Txt_Location & "' " _
           & "FROM Tbl_Display"

 

[Lol999]

This is where I struggle all the time, the ' and ". Can't find a good resource to explain them.

Thanks I'll give it a try.