code not working in bound controls on one form, but is working properly on another form

[John_G]

Going back to post #8 -

This line is incorrect: If IsNull(Me.ReceivedBy) Then Me.txtReceivedBy = [Forms]![LoginForm]![cboUser].[Column](1)



"Column" cannot be in square brackets - it is a property, not a field or control name, and properties can't be in square brackets.

It should be: If IsNull(Me.ReceivedBy) Then Me.txtReceivedBy = [Forms]![LoginForm]![cboUser].Column(1)

[ShaunG]

Going back to post #8 -

This line is incorrect: If IsNull(Me.ReceivedBy) Then Me.txtReceivedBy = [Forms]![LoginForm]![cboUser].[Column](1)

"Column" cannot be in square brackets - it is a property, not a field or control name, and properties can't be in square brackets.

It should be: If IsNull(Me.ReceivedBy) Then Me.txtReceivedBy = [Forms]![LoginForm]![cboUser].Column(1)

To easy John thanks for clearing that up and it has sorted it nicely so cheers... s

o does the debug > Compile pick up on these things?

[Minty]

Compiling will pick up on incorrect spellings and missing controls.

Put an error in deliberately and see if it spots it?

Always make sure you have Option Explicit at the top of EVERY code module.
You can make it the default in the database vba editor tools¬options require variable declaration.

[ShaunG]

Compiling will pick up on incorrect spellings and missing controls.

Put an error in deliberately and see if it spots it?

Always make sure you have Option Explicit at the top of EVERY code module.
You can make it the default in the database vba editor tools¬options require variable declaration.

no worries, thanks ill try it out and add the declaration if not already.. what does it do?

[Minty]

It forces you to declare every variable you use e.g.

Dim sSomeString as String
Dim iSomeInteger as Integer

This is important as it will a) force data types correctly for their use. b) means the compiler will spot typo's for you, which will mean your code will more likely work as you expect it to.

[ShaunG]

yeah right... I have implemented it....


Are you aware if you can insert data from one form, to 2 different tables,

e.g. - jobdetails (parent) Joineryunit (subform) and orders (subform) another form OrderDetails, also NewOrders

at the moment the data is filtered from jobdetails to joineryunit with jobnumber

when I pressed on a joineryunit it shows all the current order numbers for that one record in the Order form.... then if you double click the order it brings up the details of that order in a separate form. which is great.

Im trying to work out if I can press on the joineryunit to bring up a NewOrder for that unit which then updates the order subform and OrderDetails form

does that make sense?

[Minty]

That sounds possible. You would need to requery the sub forms once the new data was entered.

[ShaunG]

That sounds possible. You would need to requery the sub forms once the new data was entered.

to be honest I got no idea where to start have most the forms inplace and hyperlinks ready to go... would you use dmax im trying to see if that will work.

how would you requery once the data was entered?

[John_G]

no worries, thanks ill try it out and add the declaration if not already.. what does it do?

Another thing variable declaration (required or not) does is to not allow Nulls to be assigned to variables decalared as anything other than Variants.

For example:

Dim strTest as string
strTest = Null

will give you a runtime error. This can be a pain or useful, depending on your point of view, but it does catch a lot of errors.

Referring to earlier post #17, I tried out using .[Column](0), with the square brackets, and the compiler did not catch it.

[June7]

ReceivedBy field must be a Long Integer number type.

I said change the GoodsEntry form RecordSource to include tblEmployees:

SELECT tblOrderDetails.*, tblEmployees.UserName FROM tblEmployees RIGHT JOIN tblOrderDetails ON tblEmployees.EmployeeID_PK = tblOrderDetails.ReceivedBy;

Instead of referencing query object, this SQL statement can go directly in the RecordSource property.


Actually, the brackets around Column are not an issue. Certainly not needed but still works. Also, if the reference is used in a textbox, Access will add the [] for you.

If IsNull(Me!ReceivedBy) Then Me.txtReceivedBy = Form_LoginForm.cboUser.[Column](0)

[John_G]

June7:

Actually, the brackets around Column are not an issue.

I think they can be an issue, depending on context. In my database I changed this:

DoCmd.FindRecord Me![country_name].Column(0), acEntire, , acSearchAll

to this

DoCmd.FindRecord Me![country_name].[Column](0), acEntire, , acSearchAll

and it gave me a runtime error "Wrong number of arguments or invalid property assignment"

But this one worked:
DuplicateLabel.Caption = Select_File.[Column](1)

until I changed it to to this:
DuplicateLabel.Caption = Me!Select_File.[Column](1)

it failed again with the same message. It looks like adding the form reference causes it.

Best to leave the square brackets off, IMO.

[June7]

Whether I used Me! or Me., VBA removed the [] for me before I could even finish typing the line. Ah, I can go back and type them in and they stay! Get the error on Me! but not Me. Use Me. to get intellisense popup tips.

However, agree best to leave [] out. Just be aware Access will add them when used in textbox ControlSource expression.

Also, the Column(x) reference will not work in query object.

[ShaunG]

Whether I used Me! or Me., VBA removed the [] for me before I could even finish typing the line. Ah, I can go back and type them in and they stay! Get the error on Me! but not Me. I always use Me. so I get intellisense popup tips.

However, agree best to leave [] out. Just be aware Access will add them when used in textbox ControlSource expression.

Also, the Column(x) reference will not work in query object.

no worries June7 thanks for that.

im slowly getting my head wrapped around it, im only writing little codes at the moment while learning off codes I find and rearrange to suit my purpose..


so column(x) doesn't work in query object is it best to record straight back to tables or is that's depending on the situation and record?

[ShaunG]

Another thing variable declaration (required or not) does is to not allow Nulls to be assigned to variables decalared as anything other than Variants.

For example:

Dim strTest as string
strTest = Null

will give you a runtime error. This can be a pain or useful, depending on your point of view, but it does catch a lot of errors.

Referring to earlier post #17, I tried out using .[Column](0), with the square brackets, and the compiler did not catch it.

yeah id rather know if there are errors in what ive created or used... yea right good to know there could still be errors if compile comes back ok.

regarding post #26

im slowly getting there with writing smaller codes and referencing controls and forms or tables.... baby steps lol

John_G in my post #21 I refer to a question you were helping me out on in another thread. it was regarding an ordernumber filter, Ive nearly got my head wrapped around the Dmax code you gave me but because of the way ive change my database I need to refer to 2 controls which are on separate tables....

ive changed the database around again this morning once again from post#21, so that all users can see all info at once. I have 3 subforms on my parent, joineryunit(subform), orders(subform) and orderDetails(subform) and jobdetails(parent)

I have linked Orders to joineryunit and in the process of linking orderdetails to orders....

im trying to work out what the best way to assign a new order to a joineryunit that is double clicked on by hyperlink, the new Order will contain the Order and Orderdetail information in one form, I was thinking the dmax you gave me would work but I need to lengthen it for the extra controls and tables.

do you think this is the right way to go?