Unbound Box to populate field in Table

[quicova]

Hi everyone



Does anyone knows how to make an unbound box populate a field in a table.

I was thinking something like this but it didn't work

Unbound box name is BoxA

Me.BoxA.Value = Me.Table1.column(1)

Those anyone have any suggestions on how to do this?

[JoeM]

If you want to populate a field in a table, why not just make it a bound field instead of an unbound field?

[TG_W]

Try the code below replacing the temporary names with your table field names and form text box.

    If IsNull(Me!BoxA) Then ErrMsg: Exit Sub
    If IsNull(Me!BoxB) Then ErrMsg: Exit Sub
    If IsNull(Me!BoxC) Then ErrMsg: Exit Sub
    If IsNull(Me!BoxD) Then ErrMsg: Exit Sub
    If IsNull(Me!BoxE) Then ErrMsg: Exit Sub


    Dim dbs As Database, rst As Recordset


    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("Select * from YourTableNameHere")
        
        rst.AddNew
        rst!BoxA = Forms!YourTableNameHere!Field1
        rst!BoxB = Forms!YourTableNameHere!Field2
        rst!BoxC = Forms!YourTableNameHere!Field3
        rst!BoxD = Forms!YourTableNameHere!Field4
        rst!BoxE = Forms!YourTableNameHere!Field5
        
        Set dbs = Nothing
        rst.Update
        rst.Close

    DoCmd.Close acForm, "YourFormNameHere"
    DoCmd.OpenForm "YourFormNameHere"

Private Sub ErrMsg()
    Dim x As Variant
    x = MsgBox("BoxA, BoxB, BoxC, BoxD, and BoxE must all be completed." & Chr$(10) & Chr$(10) & "Please check the data entry and re-submit.", , "Invalid Entry")
End Sub

Assuming 5 entry boxes, I added some code that would check to see if the value is null. This would only be necessary if the field must be filled, such as if it were the primary key. You can add and remove as needed. The "ErrMsg" is a Private Sub that would need to be entered into the form's VBA code, as well. That is the code in the second code group. You can just copy and paste it after writing the data entry portion, but there will be an error if you reference "ErrMsg" and there is no code for it.

The last two lines "refresh" your form. Since the code writes the entered data into the table, closing and reopening the form is one quick way to clear the entered data. You could also add the code below after "rst.Close" instead of closing and opening the form.

    BoxA = Null
    BoxB = Null
    BoxC = Null
    BoxD = Null
    BoxE = Null

It appears you are coming from the Excel world with the reference to Column(1) in the code you posted. In Access, Columns = Fields, and you reference the Fields by their name. The Columns(1) would refer to the second column in a combo box that queried more than one field (Access starts counting at 0 in that instance).

[TG_W]

If you want to populate a field in a table, why not just make it a bound field instead of an unbound field?

Joe,

I've found writing from unbound boxes helpful in some instances where I have multiple users entering data into a form at a high volume, or in a budget management database I wrote, I used the code to make two records with eight fields from a form with six text and combo boxes - one record for a debit from one account and one record for a credit to another.

If quicova doesn't have a multi user instance, though, it would probably be better to have the form be a data entry form, if that is where you were going. To do that, quicova, then you would make the text box bound and set 'Data Entry' to yes in the 'Property Sheet' under the 'Data' tab. It will force to create a new record.

[quicova]

Hi JoeM

Thanks for the code, I think thats what I need
I have some questions though, I keep getting error

this is what I wrote based on your code

Dim dbs As Database, rst As Recordset




    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("Select * from Part_Database")
        
        rst.AddNew
        rst!txtDate_Created = Forms!Part_Database!Date_Created
    
    Set dbs = Nothing
    rst.Update
    rst.Close

So table name is Part_database
Form Name is New_Part_From_Existing
The only unbound text box name is txtDate_Created

I need to use the value in that text box which is a date and populate the table Part_Database in the Field Date_Created

I created a form to duplicate a record, so when adding a new record if it is similar to a record already existing I press a bottom which pops up a form with every box bounded to Part_Database.
However the Date_created field is also from the old record, I want to make it be the new date, so default value would be Date() thats why I made an unbound text box with the default value date()

I wrote that code on Form event on Close

I get the error can't find form Part_Database in the line

rst!txtDate_Created = Forms!Part_Database!Date_Created

Any ideas?

I also tried:

CurrentDb.Execute "INSERT INTO Part_Database(Date_created) " & "VALUES(" & txtDate_Created & ")"

But that doesn't give me any erros but also doesn't do anything

[TG_W]

Try brackets at the exclamation marks -

[Forms]![New_Part_From_Existing]![Date_Created]

The form name should be in the middle not the table name. You are calling a form object with [Forms].

[TG_W]

I forgot to mention, for reference, that I kick off the code with a command button. Just noticed you set to run On Close.

[TG_W]

Sorry. I keep getting interrupted and was trying to just reply quickly. Use this code

rst!Date_Created = [Forms]![New_Part_From_Existing]![txtDate_Created]

You had it in there backwards. The record set (rst) is the table you are updating, so the "rst!" should have the table field name. Everything to the right of the equal sign is referencing the form.

[quicova]

Ahhhh yes I had it backwards

I see.
I almost worked now I'm creating 2 new records, a duplicate with no date and an empty record with just the date.
But I feel I'm getting closer.

I think the problem is I'm using unbound box in a form with also bound boxes.
The bottom I did to open the form has a macro to select the record, copy, goto new record, paste, open form.
Them o the form I have another bottom with save record and close form.

I tried to remove the rst.Addnew but it crashes, is there a way to say something like rst.selectCurrent

[TG_W]

I almost worked now I'm creating 2 new records, a duplicate with no date and an empty record with just the date.

That is why I use the IsNull check and initiate through a button. The record set was one of the earliest codes I used, and with some trial and a lot of error, I found I was creating blank records left and right. Since I found the IsNull code, I haven't had an issue with blanks.

I think the problem is I'm using unbound box in a form with also bound boxes.

So is the date field in the same table as the rest of the fields? If so, you can bind it and set the 'Default Value' to Date() in the 'Property Sheet' under the 'Data' tab. This will allow for the date to automatically populate once the user creates a new record via the bound fields.

[quicova]

The is null will simply exit the sub, so it will not add the date.

So is the date field in the same table as the rest of the fields? If so, you can bind it and set the 'Default Value' to Date() in the 'Property Sheet' under the 'Data' tab. This will allow for the date to automatically populate once the user creates a new record via the bound fields.

That is what I did in the beginning, but because I already have values there from the copy record the default value is overwritten by the give value of the copy.
That was the reason to do an unbound text box to set the default value

[TG_W]

It will exit the sub, yes, but it should warn the user that the field is blank, as well, giving them the chance to update

Ok. I did not realize you were copying records. Is that also through VBA? If so, you can "set" the default value in the VBA to overwrite the value from the previous after you copy it. Just insert the code below after the copy code.

me.txtDateCreated = Date()

[quicova]

No the copy is done on a macro for the bottom
I only started with scripting and access on Monday, I'm still very new to this

I'm trying to change your original code to instead of create new, edit current record

I also tried to put in the macro where I do the copy that code but it didn't work.
It gives me the error "The object doesn't contain the Automation Object Me." so I removed Me same error though

Keep trying I guess. Looks like something so simple but I have been for hours on this

[TG_W]

Check out the VBA code to duplicate in this example I have.

After you open the database, click "Inventory". The code should be in the AfterUpdate of the "Duplicate" button. As it is in there, it gripes about some duplication errors, but the table has multiple keys.