Can't get to next record...

[VBAWTB]

I have 5 fields on a form used for inputing a new record. 3 of them are autofilled with data from a different table than what the form is linked to...and the other 2 are user filled. Only 4 of the fields get INSERT(ed) INTO the table that the form is linked to. At first, the 2 fields the user entered into were bound to the table, but I couldn't get all 4 fields to save into a single record. So I had to make them all unbound and use SQL to save the record in order to for it to work right. I used the last field's AfterUpdate event to run the SQL, but now I can't get it to trigger the next new record...if I am not making any sense, please let me know..

here is my code...

Public Month_Name As String
Public Sequence As Integer
Public Openlbs As Double
Public Closelbs As Double

Private Sub Item_NotInList(NewData As String, Response As Integer)
Response = fNotInList(NewData, "tblItem", "Item")
End Sub

Private Sub Form_Load()
Call dbInitialize 'the recordset is created in a module
rstEOM_Count.MoveFirst
txtSequence.SetFocus 'txtsequence is a field populated by the recordset
txtSequence.Text = rstEOM_Count!Sequence 'populating the first new record with data from the recordset
Sequence = txtSequence.Text
txtDesc.SetFocus 'txtDesc is a field populated by the recordset
txtDesc.Text = "'" & rstEOM_Count!ItemDescription & "'"
cboMonthName.SetFocus
cboMonthName.Text = MonthName(DatePart("m", Date) - 1) 'setting a default value for the combobox
Month_Name = cboMonthName.Text
txtOpeninglbs.SetFocus 'txtopeninglbs is the first field the user enters
End Sub

Private Sub txtClosinglbs_AfterUpdate() 'this is the last field on the form
Dim Sql As String

Closelbs = txtClosinglbs.Text 'txtClosinglbs is the other field the user enters

Sql = "INSERT INTO tblMonthly_Item_Total ([EOM_Month_Name], [SequenceID], [Openinglbs], [Closinglbs])" & _
" VALUES ('" & Month_Name & "', " & Sequence & ", " & Openlbs & ", " & Closelbs & ");"
DoCmd.RunSQL Sql 'saving the record to the table

With rstEOM_Count 'populate the next new record with data from the recordset
If Not .EOF Then
.MoveNext
txtSequence.SetFocus
txtSequence.Value = !Sequence
Sequence = txtSequence.Text
txtDesc.SetFocus
txtDesc.Value = "'" & !ItemDescription & "'"
txtOpeninglbs.SetFocus
Else
.MoveFirst
End If
End With

End Sub

Private Sub txtOpeninglbs_AfterUpdate()
Openlbs = txtOpeninglbs.Text
End Sub


Hope this makes sense...I'm really lost on this..

 

[vbaInet]

I have 5 fields on a form used for inputing a new record. 3 of them are autofilled with data from a different table than what the form is linked to...and the other 2 are user filled. Only 4 of the fields get INSERT(ed) INTO the table that the form is linked to. At first, the 2 fields the user entered into were bound to the table, but I couldn't get all 4 fields to save into a single record. So I had to make them all unbound and use SQL to save the record in order to for it to work right. I used the last field's AfterUpdate event to run the SQL, but now I can't get it to trigger the next new record...if I am not making any sense, please let me know.

You haven't clearly explained yourself. There are some contradictory statements.

Why couldn't you get all the fields saved into the same table bound to the form?

If you need to display and manipulate data from a different source, why not use a subform?

Also, for future posts, please enclose your code in code tags -->

http://www.access-programmers.co.uk/forums/showthread.php?p=1009015#post1009015

 

[VBAWTB]

I kinda figured I didn't make much sense...

I have 5 txt boxes on a form...

EOM, Sequence, Description, Opening lbs, Closing lbs
('EOM' (end of month) is a combobox bound to a lookup table)

'EOM', 'Sequence', and 'Description's values are taken from a recordset I created from a different table (not bound to the form) and 'EOM' and 'Sequence' need to be saved with the record being created. ('description' is only there for the user's benefit)
Then the user enters data into 'Opening lbs' and 'Closing lbs' and the record is complete. When the next new record appears, 'EOM' stays the same, 'Sequence' and 'Description' are filled with the next record from the recordset and 'Opening lbs' has the focus.

What's not happening...I am not getting a new record when the first data is entered into the current record. Nor is the AfterUpdate event being triggered when the last field looses it's focus.

As far as saving into a single record...EOM is a combobox that is bound to a lookup table and so I had to run a SQL statement to get the data stored into the record. But when it ran, it stored the combobox data on a different record from the bound txtboxes in the same record. So I unbound the other txtboxes and saved everything at once in a SQL statement. Is there a better way to do this?

Assuming I made what I am trying to do here any more understandable, does Access have a way to do this without all the trouble I am putting into it?

Here is my code again (with the code tags...sorry I didn't know about them)

Public Month_Name As String
Public Sequence As Integer
Public Openlbs As Double
Public Closelbs As Double

[COLOR=royalblue]Private Sub[/COLOR] Item_NotInList(NewData As String, Response As Integer)
Response = fNotInList(NewData, "tblItem", "Item")
[COLOR=royalblue]End Sub[/COLOR]

[COLOR=royalblue]Private Sub[/COLOR] Form_Load()
Call dbInitialize [COLOR=seagreen]'the recordset is created in a module
[/COLOR]rstEOM_Count.MoveFirst
txtSequence.SetFocus [COLOR=seagreen]'txtsequence is a field populated by the recordset
[/COLOR]txtSequence.Text = rstEOM_Count!Sequence [COLOR=seagreen]'populating the first new record with data from the recordset
[/COLOR]Sequence = txtSequence.Text
txtDesc.SetFocus [COLOR=#2e8b57]'txtDesc is a field populated by the recordset[/COLOR]
txtDesc.Text = "'" & rstEOM_Count!ItemDescription & "'"
cboMonthName.SetFocus
cboMonthName.Text = MonthName(DatePart("m", Date) - 1)[COLOR=seagreen] 'setting a default value for the combobox
[/COLOR]Month_Name = cboMonthName.Text
txtOpeninglbs.SetFocus [COLOR=seagreen]'txtopeninglbs is the first field the user enters
[/COLOR][COLOR=royalblue]End Sub[/COLOR]

[COLOR=royalblue]Private Sub[/COLOR] txtClosinglbs_AfterUpdate() [COLOR=seagreen]'this is the last field on the form
[/COLOR]Dim Sql As String

Closelbs = txtClosinglbs.Text [COLOR=#2e8b57]'txtClosinglbs is the other field the user enters[/COLOR]

Sql = "INSERT INTO tblMonthly_Item_Total ([EOM_Month_Name], [SequenceID], [Openinglbs], [Closinglbs])" & _
" VALUES ('" & Month_Name & "', " & Sequence & ", " & Openlbs & ", " & Closelbs & ");"
DoCmd.RunSQL Sql [COLOR=seagreen]'saving the record to the table
[/COLOR]
With rstEOM_Count [COLOR=seagreen]'populate the next new record with data from the recordset
[/COLOR]If Not .EOF Then
.MoveNext
txtSequence.SetFocus
txtSequence.Value = !Sequence
Sequence = txtSequence.Text
txtDesc.SetFocus
txtDesc.Value = "'" & !ItemDescription & "'"
txtOpeninglbs.SetFocus
Else
.MoveFirst
End If
End With

[COLOR=royalblue]End Sub[/COLOR]

[COLOR=royalblue]Private Sub[/COLOR] txtOpeninglbs_AfterUpdate()
Openlbs = txtOpeninglbs.Text
[COLOR=royalblue]End Sub
[/COLOR]

Thanks in advance for any help you can give me =]

 

[vbaInet]

Let's forget about your code for a minute and talk about the approach.

You said that three fields, i.e. 'EOM', 'Sequence', and 'Description's, are from a different source? So why the convoluted code? Why don't you use a subform?

Can you also show me a screen shot of the relationships between the tables that pertain to this form.