AutoNumber and acNewRec 2

[DajTwo]

In access 2003, I have a table [tbl_audit_#_gener] with the followign fields

audit_# : Autonumber
ccan: Unique ID of each customer record
audit_gate: Date that the audit # was generated ( now())

I require that after the user made a selection on a combo box, the table above described create a new record, saves the CCAN selected in the combo box and the date/time when that occured.

Can someone tell me where to start (other than a full l blown course in VBA?)

Thanks

 

[pcast01]

Hello DajTwo,

There are a few different ways to do this. First of all,

What version of MS Access are you using?

If you are using 2003 and lower then I might be able to help you. I know nothing about MS Access 2007...

Have you created a form? If so you can the Record Source equal to the table tbl_audit_#_gener and have some built in functionality where you would not have to really code anything.

I guess more details would help..

Paul

CHAOS, PANIC, & DISORDER - my work here is done.

I currently use Access 2003.

 

[TheAceMan1]

How are ya DajTwo . . .

Have you tried a [blue]bound form?[/blue]

The [blue]After Update[/blue] event of the combo could update [blue]audit_date[/blue] more precisely.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[DajTwo]

Hi Ace and Paul.

You are right, I have not provided sufficient info for you nice people to help me.

Access 2003, Front/Back end Configuration

This is the code for the drop down. THe CCAN is also on the drop down.

Private Sub Combo66_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[cust_name] = '" & Me![Combo66] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Lets see if I get this straight. The user selects the proper customer, then press a command button with
'After update' property is = [audit_date] = now(), [tbl_audit_#_gener] = [ccan] of the combo box.

I do not understand the bound terminology to help me create a new record.

Hope this is a bit clearer.

 

[pcast01]

DajTwo,

All that code does is find the record based on the combo box value.

I have a way to do what you were asking for but I am sure there is a better way to do this...

This is only assuming that the Form is bound to the table that you want.

Basically what I do is save all the values, create a new record and then put all the values back in and save the new record. I only save the values before I add a new record because it blanks all controls out when adding a new record on the form.

Here is an example.

Private Sub comboCCAN_AfterUpdate()
    Dim cmbValue As String
    Dim dateVal As Date
    
    dateVal = Now()
    cmbValue = Me.comboCCAN.Value
    
    DoCmd.GoToRecord , , acNewRec
    
    Me.comboCCAN.Value = cmbValue
    Me.txtDate.Value = dateVal
    DoCmd.Save
    
End Sub

Anyone else got another method?


CHAOS, PANIC, & DISORDER - my work here is done.

I currently use Access 2003.

 

[DajTwo]

Paul,

This is what I came up with your suggestion however I get a Null value as I am unable to retreve the value selected in the combobox.

This is the code for the combo box query (Combo8)

SELECT [qry_frm_audit_#_gener_combo_select].cust_name, [qry_frm_audit_#_gener_combo_select].ccan FROM [qry_frm_audit_#_gener_combo_select] ORDER BY [cust_name];

This is the code for the command button.

Private Sub Command10_Click()

' Set variables & values
Dim cmbvalue As String ' For CCAN
Dim dateval As Date 'For date

'THAT IS WHERE the null error comes from I cannot grab the value form the combobox.

cmbvalue = Me.Combo8.Value
dateval = Now()

' Select the proper table

DoCmd.OpenTable [tbl_audit_#_gener]

' Add a new record

DoCmd.GoToRecord , , acNewRec

' Assign the values to the fields in the table

[tbl_audit_#_gener]![ccan] = cmbvalue
[tbl_audit_#_gener]![audit_date] = dateval
DoCmd.Save

End Sub


Can you help correcting that poor excude of a code?

Thanks

 

[TheAceMan1]

DajTwo . . .

A [blue]bound[/blue] form is a form who's [blue]recordsource[/blue] property has a value. This property value can be:
[ol][li]A [blue]table[/blue] name.[/li]
[li]A [blue]query[/blue] name.[/li]
[li]An [blue]SQL[/blue] statement.[/li][/ol]
If the [blue]recordsource[/blue] property has [blue]no value[/blue] the form is [blue]UnBound![/blue]

Bound or unbound determines what to do here:
[ol][li][blue]Bound[/blue] saves directly to the table!.[/li]
[li]If [blue]UnBound[/blue] an SQL Insert Query will do the trick.[/li][/ol]
Also, in what column of the combobox does [blue]CCAN[/blue] reside?

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[DajTwo]

Thanks for the course and if I understood well, the form is unbound as it's purpose is to hold a combo box and a command button.

To recap:

The form has a combo box where the client is selected (CCAN is the unique ID)

SELECT [qry_frm_audit_#_gener_combo_select].cust_name, [qry_frm_audit_#_gener_combo_select].ccan FROM [qry_frm_audit_#_gener_combo_select] ORDER BY [cust_name];

The goal is to create a new record in tbl_audit_gener and save it.

audit_#: Auto generated
ccan: should equal the ccan in the combo box
audit_generated_date: now()

I am still trying to write this code.. you gave me more ammo in understanding bound and unbound.. thanks

 

[pcast01]

Ace is right DajTwo...

Does your form have a Record Source?

If so you can just use that.
I would suggest making the record source the table that you are modifying.

As far as the combo box you must set the exact column of the value that you want to save in the new record.

For example: If the Select Query that you have only returns one row then the BOUND COLUMN property would be set to 1.

CHAOS, PANIC, & DISORDER - my work here is done.

 

[DajTwo]

Thanks guys for the additional knowledge you gave me. Understandign the bound/unbound and setting the record source will help me greatly

The form now works however the data is only visible in the table when the form has been closed. Is this normal or can I correct this?

' Set variables & values

Dim cmbvalue As String ' For CCAN
Dim dateval As Date 'For date

cmbvalue = Me.Combo8.Value
dateval = Now()

' Select the proper table

DoCmd.OpenTable "tbl_audit_gener"

' Add a new record

DoCmd.GoToRecord , , acNewRec

' Assign the values to the fields in the table

ccan = cmbvalue
audit_generated_date = dateval
DoCmd.Save
DoCmd.Close

End Sub

 

[pcast01]

Yes that is normal. The table won't be available till after adding the field.

CHAOS, PANIC, & DISORDER - my work here is done.

 

[DajTwo]

Ok, thanks!!!

 

[DajTwo]

Paul,

I used what you showed me in other situations and all is working well.

On the same subject and code, I have the need to store in a variable the audit_# field, which is Auto generated by access so that I can put the contents into another table in the same routine.

Since the record is not created until the form is closed and the form has for recordsource the table, how can I retrieve that field?

I have tried several things to no avail.


Thanks in advance

 

[Yusrina]

Hi,

I'm Using Access 2003, recently I'm trying build one programme base on access. I have one table name Tbl_Employee. I'm trying to do the autonumber Id for ID_Employee such as "PEK-0001". But unfortunately the Id I have to key in every time I'm entering new data in form. Could someone give me some advise.

Thank you.

 

[DajTwo]

Hi,

Have you tried the autonumber selection in the design view of your table?

If I did not say it before, I really appreciate the time and expertise of the users on this forum.

 

[TheAceMan1]

DajTwo . . .

Going back in threads a little:

[blue] . . . if I understood well, the form is unbound as it's purpose is to hold a combo box and a command button.[/blue]

Wrong! ... [blue]the form is unbound because it has no RecordSource[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[DajTwo]

I figured that one out!! but thanks for the post..

If I did not say it before, I really appreciate the time and expertise of the users on this forum.