how to increment a field in a form when the number is used

[lewie]

I have 2 tables one btv with title tapetype and tapeid
one called type table with tapeid type and last#.
I have a form with title tapetype and tapeid fields. First i enter a title. then when i enter a tapetype i want it to generate a tapeid consisting of tapetype + last # so each tapetype will have its on set of unique numbers . Then i need to increment the last #.
I placed in the following code in the afterupdate event:
Private Sub tapetype_AfterUpdate()
If IsNull([tapetype]) Then

Else
MsgBox [type table]![Type]
If [tapetype] = [type table]![Type] Then
[btv table]![tapeid] = [type table]![Type] + [type table]![last #]
[type table]![last #] = [type table]![last #] + 1
End If
End If
End Sub

I get the right value in [tapetype] but it can't find the values in the table, [type table]![type].
Any help will be appreciated.
Thanks
Lewie

 

[JunglesMcBeef]

First of all, if change your If IsNull([tapetype]) then to If Not IsNull([tapetype]) you will eliminate the need to have the Else (unless of course you deliberately left out a section of code after the if statement). You need to update the table before you can start manipulating any of its contents, can you tell me the exact error number and/or message and which line it stops at?

 

[lewie]

I need the following code to select the right record to get the right last value to increment. When referring to a field in a db what is the appropriate syntax.

Private Sub type_AfterUpdate()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim last_numb As Integer
Stop
last_numb = 0
Set db = CurrentDb()
Set rs = db.OpenRecordset("type table&quot
last_numb = [last #] + 1 'this needs to refer to the
'correct record
[tapeID] = [type].Column(1) & last_numb ' is the name of the combo box where selected
[last #] = last_numb
Set rs = Nothing
Set db = Nothing

End Sub
I am having trouble referencing the correct record to increment the correct last # value.
TIA
Lewie

 

[JunglesMcBeef]

Hey again,

To reference a field in the rs recordset, just add the prefix rs! to your [FIELD] (eg rs![last #]). Also you can reference to it with rs.Fields("last #&quot. You should try not to create field names with spaces or special characters in them though. Saves a hassle of having to encase the field name with brackets when referencing to it.

 

[lewie]

Yeah i noticed. I had to use
rec.edit
rec("field&quot=new value
rec.update
to change the value.

I have a form with a combo box. I reference my lookup table for the values shown. they don't show up in the box. I had to place a field by that name in the table i reference in the form recordsource to get it to go into the box. Is there another way around this when you have a recordsource for the other boxes in the form. I tryed leaving control source blank on my combo box but it wouldn,t let the value from the list go into the box. I'm thinking maybe if the form didn't have a recordsource it wouldn't do this.
Thanks
Lewie

 

[lewie]

I made a new form with combo box and record source on form had no effect???? What would cause the combo box not to insert data displayed on the control; into the control???
when you select the down arrow the data is there but it won't go into the box.
Thanks Lewie

 

[lewie]

I had the wrong field displaying---np.
Lewie

 

[lewie]

I have a procedure which updates a field in the afterupdate event. How can I change the form to read only after the procedure runs to keep them from changing something they enter from this form.
rec.form.something = true
what property would that be.
I will make another button to edit this form.
Thanks
Lewie

 

[JunglesMcBeef]

If you want the form to be read only, you need to set the following properties to:

Me.AllowDeletions = No
Me.AllowAdditions = No
Me.AllowEdits = No

 

[lewie]

I have a new problem. I have a db with a title and a type. There are 4 types. If I want to display(maybe using a report) a title on one line with the data from the 4 types having that title how would i go about it. Normally i display one record per line but this would be 4 records per line. I am having trouble conceptualizing this. do i have to create a table first or is there a method to do this.
Thanks
Lewie

 

[JunglesMcBeef]

Ok,

"...a db with a title and a type. There are 4 types." ????

What has the title of the db got to do with it? and types of what? what do you mean by the types have the title (isn't the title the title of the db??)?

I've never heard the words "title" and "types" used in DB terms... (except data types, unless you are talking in a code sense and declaring a user defined type)

I don't understand what you are trying to achieve. Can you give me any more detail?

 

[lewie]

I have videotapes. There is a title for the videotape.
For that title the are 4 types ie letterbox, high def, dolby e and full frame stereo. each one of these is a recordin my db. title one hd, title one ffs, title one letterbox. and title one dolby e. I would like to print these on one line title one hd ffs letterbox and dlby e.
so i want to print 4 records across all for the same title.
Thanks
Lewie

 

[JunglesMcBeef]

I am guessing that Title and Type are field names then? I think I understand what you are trying to do, but I'm not sure if it is possible to achieve in a report. Maybe you could do it with code? I don't know if you can do it in the report but it would be achievable in a form. All you would have to do is code it to fill your controls (text box or label, or other) with each record value, simple to do with VBA.

 

[lewie]

Ok i set up a form with a combo box(title) to select the title and 4 text boxes type 1 through 4 named type1, type2 , type3, type4. I have a table named master with fields Title type and date. I have a type table with type and typeid. i have the following code in the after update of the title combo box:

Private Sub Title_AfterUpdate()
Dim db As DAO.Database
Dim rec As DAO.Recordset

Set db = CurrentDb()
Set rec = db.OpenRecordset("master&quot 'use the master table
Stop
' how do i select the right records to put in the
' right fields

[type1] = rec("type&quot

' Refresh
Set rec = Nothing
Set db = Nothing
End Sub

I don't know how to select the 4 fields related to each title. I imagine a loop or case statement but i'm not sure how to increment.
Thanks
Lewie

 

[JunglesMcBeef]

I am a bit sketchy on how this will work, but if you haven't already, add in some error trapping code that will return the err.number and err.description so we can diagnose the exact problem each time. The code I have come up with will go through every record in the table until all types have been entered into the text boxes or there are no records left in the table, filling the text boxes according to the condition in the case statement. Use the following code directly after you set the rec object (remember to declare the intCount variable):

intCount = 1
Title.SetFocus
rec.MoveFirst
Do Until rec.EOF Or intCount = 5
If rec![Title] = Title.Text Then
Select Case intCount
Case 1
type1 = rec![Type]
Case 2
type2 = rec![Type]
Case 3
type3 = rec![Type]
Case 4
type4 = rec![Type]
End Select
rec.MoveNext
intCount = intCount + 1
Loop

I am not expecting this to work first time, but you should be able to get somewhere with it... sorry bout the late reply, holidays!

 

[lewie]

It's pretty neat but for some reason when rec!type changes it changes all four text boxes????? Like there is a hard wired pointer or something. Any ideas??

 

[JunglesMcBeef]

Can you please give me some examples of data in your tables, and how this data is being displayed in the text boxes? Are all the values in the text boxes coming up as the same value or something else? Are you sure AfterUpdate is the event you want to be assigning the code to? What do you mean by "when rec!type changes" ? Is this when you change the value? or when the value changes during execution of the code?

 

[lewie]

Sub title_AfterUpdate()
Dim db As DAO.Database
Dim rec As DAO.Recordset
Dim intcount As Integer
Dim test1 As String

Stop
intcount = 1
Set db = CurrentDb()
Set rec = db.OpenRecordset("master&quot
title.SetFocus
rec.MoveFirst
Do Until rec.EOF Or intcount = 5
If rec![title] = title.Text Then
Select Case intcount
Case 1
test1 = rec![type]
type1 = test1
Case 2
type2 = rec![type]
Case 3
type3 = rec![type]
Case 4
type4 = rec![type]
End Select
End If
rec.MoveNext
intcount = intcount + 1
Loop
Set rec = Nothing
Set db = Nothing
End Sub

the data is
title type start date
one hd 1/1/2003
one lbx 1/2/2003
one pro 1/3/2003
one ffde 1/4/2003
two hd 2/1/2003
two lbx 2/2/2003
two pro 2/3/2003
two ffde 2/4/2003

when rec![type] changes it changes all text boxes type1 through 4 even though the case statement works properly and all values end up the same as the last value entered into rec![type]. its like they all are the same address.
Thanks again.
Lewie

 

[lewie]

Do I need to say "by val" somewhere.

 

[JunglesMcBeef]

Sorry again about the late reply, I have been quite busy. I have come up with the following (a couple of changes from the code you posted):

Private Sub title_AfterUpdate()
Dim db As DAO.Database
Dim rec As DAO.Recordset
Dim intCount As Integer
Dim test1 As String

intCount = 1
type1 = ""
type2 = ""
type3 = ""
type4 = ""
Set db = CurrentDb()
Set rec = db.OpenRecordset("lewie&quot
title.SetFocus
rec.MoveFirst
Do Until rec.EOF
If rec![title] = title.Text Then
Select Case intCount
Case 1
test1 = rec![Type]
type1 = test1
Case 2
type2 = rec![Type]
Case 3
type3 = rec![Type]
Case 4
type4 = rec![Type]
End Select
intCount = intCount + 1
Else
intCount = 1
End If
rec.MoveNext
Loop

Set rec = Nothing
Set db = Nothing

End Sub

I have tested this with the exact data you have given me and it works fine. Hopefully it works this time!!!